Button to create duplicate sheet adds table of contents row when pressed

Question

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 column A of the table of contents would have =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

Answer 1

demo:

function createSheetsFromMaster() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName("MASTER");  // change sheet name
  var dataRange = masterSheet.getRange("A2:A");            // change range with sheet names
  var data = dataRange.getValues();
  
  var formulasB = [];
  var formulasC = [];
  var formulasD = [];
  var formulasE = [];

  for (var i = 0; i < data.length; i++) {
    var sheetName = data[i][0].trim();  // Get the sheet name and trim any whitespace
    
    if (sheetName) {
      // Create the new sheet if it does not exist
      if (!spreadsheet.getSheetByName(sheetName)) {
        spreadsheet.insertSheet(sheetName);
      }
      // Set the formulas in the corresponding cells in columns B, C, D, and E
      formulasB.push(['=\'' + sheetName + '\'!A1']);
      formulasC.push(['=\'' + sheetName + '\'!B3']);
      formulasD.push(['=IF(\'' + sheetName + '\'!D5>=0; "Yes"; "No")']);
      formulasE.push(['=COUNTA(\'' + sheetName + '\'!B21:B123)']);
    } else {
      // Clear the formulas in columns B, C, D, and E if the corresponding cell in column A is empty
      formulasB.push(['']);
      formulasC.push(['']);
      formulasD.push(['']);
      formulasE.push(['']);
    }
  }

  // Apply all the formulas at once
  masterSheet.getRange(2, 2, formulasB.length, 1).setFormulas(formulasB); // row, column
  masterSheet.getRange(2, 3, formulasC.length, 1).setFormulas(formulasC);
  masterSheet.getRange(2, 4, formulasD.length, 1).setFormulas(formulasD);
  masterSheet.getRange(2, 5, formulasE.length, 1).setFormulas(formulasE);
}

usage:

• enter sheet name in column A
• press button
• if sheet with value in A does not exist, it will be created
• after sheet creation B, C, D and E column of MASTER sheet will be populated with formulae referencing to created sheet
• if sheet name gets deleted from A column, formulae on that row will be also deleted