jezra jezra - 4 months ago 37
Javascript Question

Get reference in Google Sheets

I'm currently copying the values of a row on sheet1 to sheet2 when the value of "1" is added to column 3. Instead of copying the values of each cell, I'd rather reference the cells on sheet1 so sheet2 is updated when the data on sheet1 is updated. Currently, I have:

function hotList(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "sheet1" && r.getColumn() == 3 && r.getValue() == "1") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("sheet2");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);
}


I'm hoping there's a just an easy alternative to copyTo I can plug in here, but any ideas are appreciated.

Answer

Thanks, @bookend - that helped get me there.

    if (s.getName() == "sheet1" && r.getColumn() == 4 && r.getValue() == "1") {
   var row = r.getRow();
   var numColumns = s.getLastColumn();
   var targetSheet = ss.getSheetByName("Hot List");
   var lastRow = targetSheet.getLastRow();
   lastRow = lastRow + 1;
   for (i = 0; i <= numColumns; i++) {
      var cellCol = String.fromCharCode(97 + i);
      var formula = "=sheet1!" + cellCol + row;
      var target = targetSheet.getRange(lastRow, 1 + i);
      target.setFormula(formula);
   }
}