Want to compare to values of each cell present in two different google spreadsheet via app script but get error

Question

i Want to compare to values of each cell present in two different google spreadsheet via app script and change the cell's background color to show that cell is updated but i am getting an "Cannot read property "0.0" from undefined. (line 16, file "Code")" i have tried opensheetbyname nd get its ranges still not workring. Googled this error no solution found Here's my code


function myFunction() {
      var sheet1 = SpreadsheetApp.openById("1-l21-
      2B7KZ5sBaRh9kmUFOyeDyP1Pnp1_IqhVlAJJR0");

      var lastRow = sheet1.getSheetByName("Sheet1").getLastRow();
      var lastCol = sheet1.getSheetByName("Sheet1").getLastColumn();
      var data = sheet1.getDataRange().getValues();

      var sheet2 = 
      SpreadsheetApp.openById("1YOQjiHZXg86XGmFZ0d5noOoLdowc_JFn3wGjICymi_o");

      var lastRow2 = sheet2.getSheetByName("Sheet1").getLastRow();
      var lastCol2 = sheet2.getSheetByName("Sheet1").getLastColumn();
      var data2 = sheet2.getDataRange().getValues();

      for(i = 0; i<=lastRow2; i++){
        for(j = 0; j<=lastCol2; j++){
         if(data[i][j] != data2[i][j]){
           sheet1.getActiveCell().setBackground("#FFFF00");
           }
          }
         }
        }

Updated Code

function myFunction() {
  var sheet1 = SpreadsheetApp.openById("1Ibyd4QbrAhLMSD_GjIQYEwGCYrbtNTbMKF7vRA-Rc9Q").getSheetByName("Sheet1");

  var lastRow = sheet1.getLastRow();
  var lastCol = sheet1.getLastColumn();
  var data = sheet1.getDataRange().getValues();

  var sheet2 = 
  SpreadsheetApp.openById("1YOQjiHZXg86XGmFZ0d5noOoLdowc_JFn3wGjICymi_o");

  var lastRow2 = sheet2.getSheetByName("Sheet1").getLastRow();
  var lastCol2 = sheet2.getSheetByName("Sheet1").getLastColumn();
  var data2 = sheet2.getDataRange().getValues();

  for(i = 3; i<lastRow2; i++){
    for(j = 1; j<lastCol2; j++){
     if(data[i][j] != data2[i][j]){
       sheet1.getRange(i + 1, j + 1).setBackground("#FFFF00");
       }
      }
     }
    }

Also i have set trigger on edit for this to work

Actual Sheet

After Code Execution Sheet

Desired Result

Tanaike Tanaike · Accepted Answer · 2018-02-20T02:12:22

data and data2 are 2 dimensional array. On the other hand, in your case, lastRow2 and lastCol2 means the length of array of data2. By this, when i and j of the for loop reach to lastRow2 and lastCol2, the error occurs. So how about this modification?

From :

for(i = 0; i<=lastRow2; i++){
  for(j = 0; j<=lastCol2; j++){

To :

for(i = 0; i < lastRow2; i++){
  for(j = 0; j < lastCol2; j++){

Note :

In your case, data and data2 have to be the same length of array. Please be careful for this.

If this modification didn't work, can you show us your sample sheet? I think that it will help to think of solution. If I misunderstand your question, I'm sorry.

Edit 1 :

There are 2 parts for modifying. In these modifications, sheet1 supposes the original sheet. Please modify as follows. Please input sheet name of original sheet.

1

var sheet1 = SpreadsheetApp.openById("1-l21-2B7KZ5sBaRh9kmUFOyeDyP1Pnp1_IqhVlAJJR0");

var sheet1 = SpreadsheetApp.openById("1-l21-2B7KZ5sBaRh9kmUFOyeDyP1Pnp1_IqhVlAJJR0").getSheetByName("### sheet name of original sheet ###");

2

sheet1.getActiveCell().setBackground("#FFFF00");

sheet1.getRange(i + 1, j + 1).setBackground("#FFFF00");

Edit 2 :

function myFunction() {
  var sheet1 = SpreadsheetApp.openById("1Ibyd4QbrAhLMSD_GjIQYEwGCYrbtNTbMKF7vRA-Rc9Q").getSheetByName("Sheet1");
  var lastRow = sheet1.getLastRow();
  var lastCol = sheet1.getLastColumn();
  var data = sheet1.getDataRange().getDisplayValues(); // Modified
  var sheet2 = SpreadsheetApp.openById("1YOQjiHZXg86XGmFZ0d5noOoLdowc_JFn3wGjICymi_o");
  var lastRow2 = sheet2.getSheetByName("Sheet1").getLastRow();
  var lastCol2 = sheet2.getSheetByName("Sheet1").getLastColumn();
  var data2 = sheet2.getDataRange().getDisplayValues(); // Modified
  for(i = 3; i<lastRow2; i++){
    for(j = 1; j<lastCol2; j++){
      if(data[i][j] != data2[i][j]){
        sheet1.getRange(i + 1, j + 1).setBackground("#FFFF00");
      }
    }
  }
}