Home > Enterprise >  Can you delete a specific sheet without knowing sheet ID?
Can you delete a specific sheet without knowing sheet ID?

Time:12-20

I have a code which does the following:

  1. Collects values from first sheet "OTW Sheet"
  2. Opens another existing sheet by Id - "Invoice"
  3. Sets values in "Invoice" as copied from "OTW Sheet"
  4. Creates a new spreadsheet in the Drive App and copies "Invoice" to new Spreadsheet.

This all works well but the next step is to delete the sheet called "Sheet1" in the newly created Spreadsheet. This part i can't get to work. No errors appear, it just fails to delete the sheet.

Code is as follows:

function myFunction() {
  
  var OrderSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OTW Sheet");
  var Row = OrderSheet.getLastRow();
  var Values1 = OrderSheet.getRange(Row, 5).getValue();
  var Cost1 = OrderSheet.getRange(1, 16).getValue();

  var Invoice = SpreadsheetApp.openById("19wv72wCNsPV6_hK8XCRcMBkfzEEU1IrJB6scuPz2Vfo"); 
  Invoice.getSheetByName("BLANK");
  
  var Newsheet = Invoice.getActiveSheet();
  Newsheet.getRange(17, 7).setValue(Cost1);
  Newsheet.getRange(1, 15).setValue(Values1);

  var NewSS = SpreadsheetApp.create('OTW Invoice'); 
  Newsheet.copyTo(NewSS).setName('OTW Invoice '   Cost1).getSheetId();
  var Folder = DriveApp.getFoldersByName("TEST Invoices").next();
  var CopyFile = DriveApp.getFileById(NewSS.getId());
  Folder.addFile(CopyFile);

  NewSS.getId();
  SpreadsheetApp.openById(NewSS).getSheetByName("Sheet1").deleteSheet();
  
}```

CodePudding user response:

Replace

  NewSS.getId();
  SpreadsheetApp.openById(NewSS).getSheetByName("Sheet1").deleteSheet();

by

NewSS.deleteSheet(NewSS.getSheetByName("Sheet1"));

Reference

CodePudding user response:

Deleting A Sheet of a newly created spreadsheet

This works:

function myFunction() {
  const ss = SpreadsheetApp.openById("ssid");
  const sh = ss.getActiveSheet();
  const nss = SpreadsheetApp.create('OTW Invoice');
  sh.copyTo(nss).setName('OTW Invoice '   ' Copy');
  const fldr = DriveApp.getFoldersByName("TestFolder").next();//assume only one
  const file = DriveApp.getFileById(nss.getId());
  fldr.addFile(file);//move nss to a subfolder
  nss.deleteSheet(nss.getSheetByName("Sheet1"));
}
  • Related