I'm just wondering if there's a way for me to rename the spreadsheet and sheet names upon running the SpreadsheetApp.create() script. I'm attempting to retrieve the value of the name in a specific cell. I'm thinking of getting the L12 value for the spreadsheet name, and the concatenated value of D16, H16, and P16 for the sheet name.

  const srcSs = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = srcSs.getSheetByName("TEMPLATE");
  const values = sheet.getRange("C2").getDataValidation().getCriteriaValues()[0].getValues().flat().filter(String);
  const dstSs = SpreadsheetApp.create("SMRtempSpreadsheet");

  SpreadsheetApp.getActive().toast("About to take some action... Please wait...");

  values.forEach(v => {
    sheet.getRange("C2").setValue(v);
    SpreadsheetApp.flush();
    const tempSheet = sheet.copyTo(srcSs);
    const range = tempSheet.getDataRange();
    range.copyTo(range, {contentsOnly: true});

    var cell = tempSheet.getRange("D19");
    cell.setNumberFormat('mm/dd/yyyy');

    tempSheet.getRange("B2:2").clear().clearDataValidations();
    tempSheet.getDrawings().forEach(e => e.remove());
    tempSheet.deleteColumn(1);
    tempSheet.deleteRow(1);
    tempSheet.deleteRow(2);
    tempSheet.deleteRow(3);
    tempSheet.copyTo(dstSs);
    srcSs.deleteSheet(tempSheet);
  });
  dstSs.deleteSheet(dstSs.getSheets()[0]);

SAMPLE SPREADSHEET

CodePudding user response：

When I saw your provided Spreadsheet, it seems that the situation is different between your provided Spreadsheet and your provided image. If you want to use the provided sample Spreadsheet, from your provided image, I thought that the spreadsheet name and sheet name might be from "L16" and "D14:S14", respectively. When my understanding is correct, how about the following modification?

Modified script:

function PrintMultiple() {
  const srcSs = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = srcSs.getSheetByName("TEMPLATE");
  const values = sheet.getRange("C2").getDataValidation().getCriteriaValues()[0].getValues().flat().filter(String);
  const spreadsheetName = sheet.getRange("L16").getValue();
  const dstSs = SpreadsheetApp.create(spreadsheetName);
  SpreadsheetApp.getActive().toast("About to take some action... Please wait...");
  values.forEach(v => {
    sheet.getRange("C2").setValue(v);
    SpreadsheetApp.flush();
    const tempSheet = sheet.copyTo(srcSs);
    const range = tempSheet.getDataRange();
    range.copyTo(range, {contentsOnly: true});
    var cell = tempSheet.getRange("D17");
    cell.setNumberFormat('mm/dd/yyyy');
    tempSheet.getRange("B2:2").clear().clearDataValidations();
    tempSheet.getDrawings().forEach(e => e.remove());
    tempSheet.deleteColumn(1);
    tempSheet.deleteRow(1);
    tempSheet.deleteRow(2);
    tempSheet.deleteRow(3);
    const sheetName = tempSheet.getRange("C11:R11").getValues()[0].filter(String).join(",");
    tempSheet.copyTo(dstSs).setName(sheetName);
    srcSs.deleteSheet(tempSheet);
  });
  dstSs.deleteSheet(dstSs.getSheets()[0]);
}

• By this modification, the created Spreadsheet has spreadsheetName name and each tab has sheetName name.

Note:

• I used the delimiter of , for the sheet name. When you want to change this, please modify join(",").

• If your actual Spreadsheet is different from your sample Spreadsheet, this modification might not be able to be used. Please be careful about this.