0

I am trying to pull data from one google sheet to another. But i am getting out of bounds error in line "sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);".

Below is my google script.Could you please assist what is the wrong with this script.What changes needs to be done.

function Pull() {
      var SpreadSheetKeyA = "1FYf9FO1nZh6cxoc9B9HYmwwAEWrXlxbFIRZJPXIJ4QQ";
      var SpreadSheetKeyB = "1p1H9rYW5pjL7yyyky6rFKgYU8s-2gWcwU_urelx6SaA";
      var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getSheetByName("Alerts API");
       var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getSheetByName("API Data");
      var startRow = 2;
      var data = sheet1.getRange(2,1,100,7).getValues();
      var time = new Date ().toJSON().slice(0,10);;
      for(var i = data.length - 1; i > -1; i--) {
        if(data[i].join("").length == 0) data.splice(i, 1);
      }
      for (var r = 0; r < data.length; r++) {
        data[r].unshift(time);
        }
  if(data.length > 0 ) {
    sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);
    sheet2.getRange(sheet2.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
    }
    }
Rubén
  • 34,714
  • 9
  • 70
  • 166
divya
  • 11
  • 4

1 Answers1

1

Tried out a couple of scenarios to test your code. I didn't change anything in the rest of the code(which I've not included below) except for passing references to my own spreadsheets.

In the first one, the code worked perfectly even when I change number of rows from 10 to 12 (each of my sheets only have 10 rows of data) in var data = sheet1.getRange(2,1,100,7).getValues(); and the specified line of code (by you) does not throw me any sort of error at all. However, if I empty out my target Spreadsheet(sheet2), it throws me the specified error.

To fix that, I modified the code in the following manner:

if(data.length > 0 ) 
  {
    if(sheet2.getLastRow() == 0)
    {
      sheet2.insertRowsAfter(1, data.length);
      sheet2.getRange(2, 1, data.length, data[0].length).setValues(data);
    }
    else{
      sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);
      sheet2.getRange(sheet2.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
    }

  }

And now everything works perfectly well. Hope this resolves your issue.

pointNclick
  • 1,584
  • 1
  • 12
  • 17