Google sheet code - Edit, Add row from sheet1 to sheet2 upon clicking a button

Question

I need your help with regard to my script below. I have 2 sheets (sheet1 and sheet2). Sheet1 contains the cell values in which this will be copied/to update cells in sheet2. The script will add and copy values from sheet1 to sheet2, but it will not recognize the condition. What i would like to happen is, if sheet2!c3 = sheet1!f5 will just update the cells, else it will add another row and copy the datas from sheet1 to sheet2.

Thanks for the help!

function emailongoing() {
   var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('OutageYellow');            
   var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');            
   var lastRow = sheet2.getLastRow()+1;

  cntr = 0

  if (sheet2.getRange('C3') != sheet1.getRange('F5')) {
  sheet1.getRange('E5').copyTo(sheet2.getRange(lastRow + cntr,2), {contentsOnly:true}); 
  sheet1.getRange('F5').copyTo(sheet2.getRange(lastRow + cntr,3), {contentsOnly:true});
  sheet1.getRange('C5').copyTo(sheet2.getRange(lastRow + cntr,5), {contentsOnly:true});
  sheet1.getRange('C7').copyTo(sheet2.getRange(lastRow + cntr,4), {contentsOnly:true});
  sheet1.getRange('C8').copyTo(sheet2.getRange(lastRow + cntr,9), {contentsOnly:true});
  sheet1.getRange('C9').copyTo(sheet2.getRange(lastRow + cntr,10), {contentsOnly:true});
    cntr++;
  }


  else if (sheet2.getRange('C3') != " ") {
  sheet1.getRange('E5').copyTo(sheet2.getRange(lastRow + cntr,2), {contentsOnly:true}); 
  sheet1.getRange('F5').copyTo(sheet2.getRange(lastRow + cntr,3), {contentsOnly:true});
  sheet1.getRange('C5').copyTo(sheet2.getRange(lastRow + cntr,5), {contentsOnly:true});
  sheet1.getRange('C7').copyTo(sheet2.getRange(lastRow + cntr,4), {contentsOnly:true});
  sheet1.getRange('C8').copyTo(sheet2.getRange(lastRow + cntr,9), {contentsOnly:true});
  sheet1.getRange('C9').copyTo(sheet2.getRange(lastRow + cntr,10), {contentsOnly:true});
    cntr++;
  }

}

Anton Dementiev Anton Dementiev · Accepted Answer · 2018-05-18T16:47:10

Take some time to learn about data types and structures in JavaScript. This is basic stuff that can save you lots of trouble.

https://medium.com/dailyjs/back-to-roots-javascript-value-vs-reference-8fb69d587a18

According to the documentation, the 'getRange()' method of the Sheet class returns the instance of the Range object.

https://developers.google.com/apps-script/reference/spreadsheet/range

Because objects in JS are passed by reference, not by value (see the 1st link), comparing these 2 instances of Range is like asking whether these objects occupy the same location in memory. Clearly, that's not the case as getRange("C3") is NOT the same as getRange("F5"). Again, in the case of objects, what's being compared is references to where they are stored in memory, not the actual properties and values in them.

On the other hand, primitive values (including strings) are passed by value, so you can compare them. However, there are caveats when you build variables as objects in the first place by explicitly calling the respective constructor. Consider this:

new Number(5) == 5 //logs true because abstract comparison is used. The object is downcast to the primitive type

new Number(5) === 5 //logs false when used with strict comparison that also compares types

new Number(5) == new Number(5) //logs false - type coersion not required, references are not equal

new Number(5) === new Number(5) //logs false  - references are not equal

You must call the getValue() or getValues() methods on the Range object to get the values that you can compare from sheet cells. Finally, iterating over the range one cell at a time is a horrible practice that will make your script extremely sluggish. Check this answer for details Copy/Push data from array into a sheet range one row at a time using Google Apps Script

Google sheet code - Edit, Add row from sheet1 to sheet2 upon clicking a button

1 Answers