Gsheet - Check last empty row and set data as array per row

Jim E Russel 0
Viewed 18

I do not know how to optimize this. I just want to check the last empty row and paste the array data horizontally. This is my current setup where I manually set the location of each row. I tried using .setValues and [[row[1], row[2]] but the problem is I am only referencing 1 row with getLastRow().

const sourceData = source.getDataRange().getValues().slice(1);
  sourceData.forEach((row, i)=> {
    if(row[0] == "") {
      return;
    } else {
      let id = row[0];
      var database = SpreadsheetApp.openById(id);
      var copyToSheet = database.getSheetByName("Sheet1");
      var lastRow = copyToSheet.getLastRow();
      copyToSheet.getRange(lastRow + 1, 1).setValue(row[2]);
      copyToSheet.getRange(lastRow + 1, 2).setValue(row[3]);
      copyToSheet.getRange(lastRow + 1, 3).setValue(row[4]);
    }
  });
google-sheets google-apps-script
1 Answers

Try this:

function lfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getDataRange().getValues().slice(1);
  vs.forEach((row, i) => {
    if (row[0]) {
      var ssi = SpreadsheetApp.openById(row[0]);
      var shi = ssi.getSheetByName("Sheet1");
      sh1.getRange(shi.getLastRow() + 1, 1, 1, 3).setValues([[row[2], row[3], row[4]]])
    }
  });
}

This will still potentially take a while because you have to wait to open up all of the spreadsheets

user30hu0
Related