AppScript: Compare a cell Value pre & post update to populate another cell

Question

Newbie to scripts.

Link to the sheet with minimal essential information

• In the status tab, there would be an inventory of equipment (currently only one (A1). The equipment can be in various states (drop down menu driven).

• The equipment state is updated with a day stamp on the test tab. That part of the script is working fine.

• What I am keen on is showing the transition stage of machine, say from running to halted , unused to in-use etc. to be reflected in the test tab (machine state transition needs refinement but for now any transition from the previous day will be marked by 1 , else 0).

What I tried

• In the AppScript trying to compare the previous value of state with current and populate the transition column. The script I tried fails completely (commented out portions refer) to show the expected output. It always shows 1,irrespective of the transition.

The logic I tried was read the value of B2, assign it to B_old, update the sheet, assign the new value of B2 to B_new and use an IF statement to compare and populate C2. There may be simpler ways to achieve this but this is all I could thick of.

var ss = SpreadsheetApp.openById("15heJq_cP35UFpC8WgbdcSfAz0eEOjtyA0odfG-UHjcI");
var sh = ss.getSheetByName("test");
var freeze = sh.getRange("2:40");
// 2nd to 40th column is of interest
freeze.copyTo(freeze,{contentsOnly:true}); 
// https://webapps.stackexchange.com/questions/80859/automatically-add-rows-daily-and-append-previous-days-data
var B_old= sh.getRange ("B2");
sh.insertRowAfter(1); 
sh.getRange("A2").setFormula('=A3+1');
sh.getRange("B2").setFormula(`=(\'Status\'! C2)`);
//var B_new= sh.getRange ("B2");
/*if (B_old!==sh.getRange("B2")) 
{
sh.getRange ("C2").setValue(1);
console.log('1');}
else 
{sh.getRange ("C2").setValue(0);
console.log('0');}*/
}

• I could apply the transition check on the test tab using an IF formula but the problem is that rows are added above and the formula can't carry over. Maybe appending rows below is a solution but I couldn't figure out how to do that either!

Though I suspect AppScript way would offer more flexibility but I am open to suggestions.

Note: To test the script with a clean slate in A2 of test tab enter today's date and run the script manually or set the trigger to on edit.

Answer 1

Self answering this, may help someone else.

• Firstly, including the formula to work in app script is better than formula based query on the generated sheet (daily tab) . It does not involve manual intervention as in the following cases

  • With rows added on top, the formula needs to be manually pasted to cover new rows added on top, so not an option.

  • With rows appended at bottom, the formula can be pasted but new rows are added after the last row, so that leaves huge gaps or manually intervention is needed, again not an option.

• What worked for me:

  • Appending rows after the last row based on this answer.

  • Using setFormula method that automatically adjusts to the new range , as described in this answer. The formula I used was this (to cater for other needs not specified in the question)

const cell = sh.getRange(`G2:G${sh.getLastRow()}`); cell.setFormula(`=if (AND($b2<>"",$b3<>"",$b2<>$b3),"1","0")`);

The entire spreadsheet and script was changed significantly (compared to what was asked in the question) but above are the essential ingredients of the solution.

As a side note, the output of the above formula ( 1 or 0) had text properties and had to be converted to numbers using Value() for charting purposes. Not sure why that happened.