I am trying to create a script that when I press a button a sheet will be created with a name from UI. I want the table of contents to add a row with the sheet name as well as formulas referencing certain cells within that new sheet.

For instance in column A would be Sheet1!$A$1 column B would be Sheet1!$B$3 column C would be IF(Sheet1!$D$5>=0,”Yes”,”No”) and column D would be CountA(Sheet1!$B$21:$B$123).

How do I add this to the next open row of the table every time I press this button and have it automatically reference the correct cell in the new sheet? I want the function to update the cell value after the new sheet is made so that it doesn’t need to run a script every time I change the referenced cell.

It needs to be able to handle spaces and apostrophes as well. If the sheet name is Sheet1 then the referenced cell will begin with Sheet1!, but if the sheet name is It’s Tuesday, the referenced cell will begin with ‘It”s Tuesday’!

I tried getting the sheet name but the string value did not work with spaces and apostrophes. I am VERY new to Apps Scripts so there is a reasonable chance I am doing it wrong but would love some guidance on the best way to get this outcome. I am wanting to avoid using get and set value functions if possible to reduce the amount of