CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Calvin Gaunce
Calvin Gaunce

Reputation: 366

Server error on simple "Delete Sheet / Insert Sheet" script

I am receiving a server error on a very simple script, and I'm not sure why. The intent of the script is to check if a sheet already exists. If the sheet does exist, it should be deleted and re-created. If it doesn't exist, it should be created.

The script works fine if the sheet doesn't exist already. If it does exist, the script will delete the sheet and then throw the following error on the line with .insertSheet("Test").

"We're sorry, a server error occurred. Please wait a bit and try again."

Any help is greatly appreciated!

function newSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = spreadsheet.getSheetByName("Test");
  if(sheet != null){
    spreadsheet.deleteSheet(sheet); 
  }
  spreadsheet.insertSheet("Test");
}

Upvotes: 3

Views: 1505

Answers (3)

JPV
JPV

Reputation: 27282

Try, passing in an index...

function newSheet() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
    sheet = spreadsheet.getSheetByName("Test");
if (sheet != null) {
    spreadsheet.deleteSheet(sheet);
    }
spreadsheet.insertSheet("Test", spreadsheet.getSheets().length);
}

and see if that works ?

Upvotes: 4

Alan Wells
Alan Wells

Reputation: 31300

I'm getting the same errors. It might be a bug. You'll probably need to use sheet.clear().

Maybe some code like this:

function newSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = spreadsheet.getSheetByName("Test");
  if(sheet != null){
    sheet.clear();
  } else { 
    spreadsheet.insertSheet("Test");
  }
}

Instead of deleting the sheet and creating another sheet of the same name, just clear everything in the current sheet. I'm not sure what advantage you'd have to deleting the sheet, clearing will hopefully give you the result you want. If not, I'm curious to know why the sheet needs to be deleted?

Upvotes: 1

Amit Agarwal
Amit Agarwal

Reputation: 11278

Maybe the script is throwing an error if the sheet doesn't exist. You can put it inside a try-catch block.

function newSheet() {

  var sheet, spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  try {
    sheet = spreadsheet.getSheetByName("Test");
    spreadsheet.deleteSheet(sheet);
  } catch (e) {};

  spreadsheet.insertSheet("Test");

}

Upvotes: 0

Related Questions