Having problem importing data from a google sheets workbook to a master workbook, code cant find first empty row

Question

I cant make the range that i selected go to the first empty row in column A in sheet 1.

Im new to Google-App-Script, and have never done anything in Java before, just read some tutorials. Im trying to automate some lab machines so that the data they return go to a master workbook.

function macro (){
var s1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange('E16:F');
SpreadsheetApp.setActiveRange(range)
var copy = s1.getRange('E16:F36').getValues();
var s2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1kXinBTHIhEf78AHe2zpSz6ugi1qgmd5WwqSg81IFrfk/edit?usp=drive_web&ouid=102308466606203090938')
var column = s2.getRange('A:A');
var values = column.getValues();
var ct = 0;
while ( values[ct][0] != "" ) 
ct++;
var lastCell = s2.getRange('E16:F36');
var paste = s2.getRange('ct:B').setValues(copy);
}

I expect the code to set the data from the source workbook to be placed in the first empty row on the master workbook in sheet 1.

RobertLCrocker RobertLCrocker · Accepted Answer · 2019-01-18T17:47:04

Need to use getLastRow() method of spreadsheet class

  var lastRow = s2.getLastRow(); 
  var newStartRow = lastRow +1;

and then add 1 to it to define first row to paste your data to.

Having problem importing data from a google sheets workbook to a master workbook, code cant find first empty row

2 Answers