Google Script - Setting formulas in pre-defined cells after automatically creating a new sheet in a spreadsheet

0
votes

Consider I have a masterSheet with names in column A and a link in colum B, starting in row 2. I'd like that for every row added in the masterSheet, it automatically :

  1. Creates new sheet that has the name of the person in column A
  2. Sets the values of A and B in this newly created sheet
  3. Sets my IMPORTRANGE formula in C
  4. Sets my CONCATENATE formula in J2:J50

The points 1. and 2. are working well. Points 3. and 4. don't work at all and I get an error message like this one : TypeError: sheetNames.getRange is not a function

I can't figure out what is the problem about this but I'm pretty sure it comes from the place I put them in the loop or something.

Can someone help me on this one ?

function createNewSheets() {
  // 1. Retrieve the current sheet names.
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames = dataSheet.getSheets().map(s => s.getSheetName());

  // 2. Retrieve the values from "mastersheet" sheet.
  var masterSheet = dataSheet.getSheetByName('Liste de nageurs');
  var values = masterSheet.getRange('A2:B' + masterSheet.getLastRow()).getValues();

  // Imports table from link
  var swimmerTable = '=IMPORTHTML(B1;"table";4)'
  // Concatenates event with pool size
  var fullName = 'CONCATENATE(C2;" - ";D2)'

  // 3. Using the retrieved sheet names and values, the new sheets are inserted and the values are put.
  values.forEach(r => {
    if (!sheetNames.includes(r[0])) {
      dataSheet.insertSheet(r[0]).appendRow(r);
      sheetNames.push(r[0]);

      // Sets the formula in the 3rd column:
      sheetNames.getRange(1, 3).setFormula(swimmerTable)

      // Sets the formula2 and iterates it from row 2 to row 50
      sheetNames.getRange("J2:J50").setFormula(fullName)
    }
  });
}
2
loopsgoogle-apps-scriptgoogle-sheetsforeachgoogle-sheets-formula

2 Answers

1
votes

Possible solution

I believe the mistake is here:

dataSheet.insertSheet(r[0]).appendRow(r);
sheetNames.push(r[0]);

// Sets the formula in the 3rd column:
sheetNames.getRange(1, 3).setFormula(swimmerTable)

// Sets the formula2 and iterates it from row 2 to row 50
sheetNames.getRange("J2:J50").setFormula(fullName)

Since you say the error you receive is

TypeError: sheetNames.getRange is not a function

Which seems to be because your variable sheetNames might look like this:

["sheet1", "sheet2", ...]

These are just "strings" (plain text) not sheet objects. So they don't have the method getRange.

You might change it to:

var newSheet = dataSheet.insertSheet(r[0])
sheetNames.push(r[0]);

newSheet.appendRow(r);

newSheet.getRange(1, 3).setFormula(swimmerTable)
newSheet.getRange("J2:J50").setFormula(fullName)

Here you are assigning the return value of dataSheet.insertSheet to a variable. The return value of this method is the new sheet object.

To summarize, I believe in your code you are mixing up the concepts of a Sheet name, which is just text, with a sheet object which has the getRange method, amongst others.

Reference

0
votes

Try this:

  const fullName = '=C2 & " - " & D2)';
      // ...
      const sheet = dataSheet.insertSheet(r[0]).appendRow(r);
      // ...
      sheet.getRange(1, 3).setFormula(swimmerTable);
      sheet.getRange("J2:J50").setFormula(fullName);