In an attempt to compare data from two Google Sheets, I am facing a challenge. Each sheet contains a column that serves as the identifier (Sheet1:H and Sheet2:C). My goal is to update the value in Sheet1:I to match the value in Sheet2:E when the identifiers match. Despite running the provided code without any errors, it does not seem to be working as intended.
While looking for solutions online, I noticed that most posts did not address the specific method of comparing different columns that I am using.
function changestatus() {
// retrieve spreadsheet A and data range
ssA = SpreadsheetApp.openById('IDHERE');
sheetA = ssA.getSheetByName('Sheet1');
dataA = sheetA.getRange('H2:H').getValues();
dataD = sheetA.getRange('I2:I').getValues();
// retrieve spreadsheet B and data range
ssB = SpreadsheetApp.openById('IDHERE');
sheetB = ssB.getSheetByName('responses');
dataB = sheetB.getRange('C2:C').getValues();
dataC = sheetB.getRange('E2:E').getValues();
for (var i = 0; i > sheetA.getLastRow(); i++) {
if (dataA[1][i] == dataB[1][i] && dataC[1][i] != dataD[1][i]){
var value = sheetA.getRange(i+1, 2).getValue(dataD);
sheetB.getRange(i+1, 2).setValue(value);
} // end if
} // end i
The initial results from both sheets would look like this:
Sheet 1
H:(ID) 1 I:(grade) pass
Sheet 2
C:(ID) 1 E:(grade) fail
After running the function:
Sheet 1
H:(ID) 1 I:(grade) fail