CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Aidan Wilson
Aidan Wilson

Reputation: 51

Can you delete a specific sheet without knowing sheet ID?

I have 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();
}

Upvotes: 0

Views: 293

Answers (2)

Cooper
Cooper

Reputation: 64100

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"));
}

Upvotes: 1

Wicket
Wicket

Reputation: 38348

Replace

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

by

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

Reference

Upvotes: 1

Related Questions