Duplicate google worksheet multiple times, rename worksheet and update specific cell based on existi-CodePudding

Have an existing code I'm using for google appscript that completes the first 2 steps which is to create multiple (google) worksheets (from a template) and rename based on values pulled from another sheet ('main_invoice'). However, I still need to update one cell in the new worksheets to reflect IDs that also exist from the same table main_invoice. Column A:A is where the IDs are stored and would like the values entered in B22 in the new worksheets. I added the line pids to get values but need to figure out how to insert them in each new sheet as they are created from a template? Any help would be appreciated. Thank you!

function TemplateDuplication()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      temp = ss.getSheetByName('blank-invoice-sample'),
      all = ss.getSheetByName('main_invoice'),
      sheets = ss.getSheets(),
      vals = all.getRange('k:k').getValues(),
      pids = all.getRange('a:a').getValues();
  for (var i = 0, len = sheets.length, names = []; i < len; i  )
    names.push(sheets[i].getName());
  for (var i = vals.length-1; i > 0; i--)
  {
    var name = vals[i][0];
    if (name !='' && names.indexOf(name) == -1)
    {
      temp.activate();
      ss.duplicateActiveSheet()
      .setName(vals[i][0])
     
    }  }}

CodePudding user response：

Try:

function TemplateDuplication() {

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheetNames = spreadsheet.getSheets().map(sheet => sheet.getName())
  const template = spreadsheet.getSheetByName('blank-invoice-sample')

  spreadsheet.getSheetByName('main_invoice')
             .getDataRange()
             .getValues()
             .map(row => [row[0], row[10]])
             .filter(row => (row[0] !== `` && row[1] !== ``) && !sheetNames.includes(row[1]))
             .forEach(([id, sheetName]) => {
               template.copyTo(spreadsheet)
                       .setName(sheetName)
                       .getRange(`B22`)
                       .setValue(id)
             })

}

Commented:

function TemplateDuplication() {

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  // Get all sheets and then 'convert' them to just the names.
  const sheetNames = spreadsheet.getSheets().map(sheet => sheet.getName())
  const template = spreadsheet.getSheetByName('blank-invoice-sample')

  spreadsheet.getSheetByName('main_invoice')
             // Get all values of `main_invoice`...
             .getDataRange()
             .getValues()
             // And 'reduce' them to just the A and K columns.
             .map(row => [row[0], row[10]])
             // Remove any rows without values, and that don't have names included in `sheetNames`.
             .filter(row => (row[0] !== `` && row[1] !== ``) && !sheetNames.includes(row[1]))
             // For each [id, sheetName] of our filtered array...
             .forEach(([id, sheetName]) => {
               // Insert a copy of the template sheet...
               template.copyTo(spreadsheet)
                       // Rename it to the sheetName...
                       .setName(sheetName)
                       // And set the cell at "B22" to the `id`.
                       .getRange(`B22`)
                       .setValue(id)
             })

}

Learn More: