Problem in adding formula in script for updating daily

Question

Newbie to sheets and scripts!

I have seen this question,and am partially successful to the extent of creating a new row with date being updated using this script

var ss = SpreadsheetApp.openById("1ewiUoG1LCvnrArEy96k6N0n4WHfR-TP77ACGQV11_Hk");
var sh = ss.getSheetByName("Daily Summary");
sh.insertRowAfter(1); 
sh.getRange("A2").setFormula('=A3+1');
}

I have nearly two dozen columns that I would like to be updated everyday at midnight. Typically the column values are of the type

• =COUNTIFS('2022'!F4:F319,"=GG-B-Recovered") , so I added this line to the above script sh.getRange("B2").setFormula('=COUNTIFS('2022'!F4:F319,"=GG-A-Active")'); (2022 is a separate tab in the same workbook. When I run this script , the new row is NOT created and this error shows Attempted to execute newrow, but could not save.

• Another kind of value is like ='Dashboard '!R17, where dashboard is yet another tab in the same book and I suspect the error is in referring to the sheet or maybe more

How do I fix this please?

Answer 1

Replace

sh.getRange("B2").setFormula('=COUNTIFS('2022'!F4:F319,"=GG-A-Active")');

by

sh.getRange("B2").setFormula(`=COUNTIFS('2022'!F4:F319,"=GG-A-Active")`);

Change done in the above line was replacing the ' (single quote, apostrophe) that encloses the formula by ` (backtick, grave accent, back quote).

The error occurred because 2022 is 'enclosed inside a string enclosed also by '. Another solution is to avoid ' enclosing 2022 (sheets name need to be ' enclosed only when they have spaces):

sh.getRange("B2").setFormula('=COUNTIFS(2022!F4:F319,"=GG-A-Active")');

---

The error

Attempted to execute newrow, but could not save

occurs when there is a syntax error that prevent the project to be saved. You have to look at all the files in the project that have not saved for this kind of errors. The unsaved files have yellow dot as shown in the following image:

When a syntax error occurs, there will be a red curly underline at some point, like the one shown under the closing curly bracket and there will be a red bar in the vertical scroll bar as shown in the following image

In this specific case, the line having the syntax error is shown as follows:

With some syntax errors the Google Apps Script IDE provide hints / fix suggestions but they might not be helpful for everyone. Pressing F8 (Go to next error in Files) will jump to the next error and display an error message like the following:

Related

• Google Apps Script Editor - is error elaboration possible?