Create a new sheet in a Google Sheets with Google Apps Script

Question

How to create a new sheet in a Google Sheets with Google Apps Script?

I know it seems obvious but I just want to create a new sheet with a specific name.

Lashley

Answer 1

Surprisingly I didn't find any clear and quick answer.

So here is my code:

function onOpen() {
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var yourNewSheet = activeSpreadsheet.getSheetByName("Name of your new sheet");

    if (yourNewSheet != null) {
        activeSpreadsheet.deleteSheet(yourNewSheet);
    }

    yourNewSheet = activeSpreadsheet.insertSheet();
    yourNewSheet.setName("Name of your new sheet");
}

Finally, note that this new sheet will be automatically the active one.

Lashley

The code is ok but you probably dont want it on onOpen. If you want to create it on user-demand create a menu. – Hyperbole

Yes you're right, this is exactly what I did just after that! – Magna

I was opening a post because every time I create a new sheet using a script the Spreadsheet stops working after a while. Does it happen to you? – Dirichlet

Hum nope sorry, never happened! – Magna

Answer 2

I'd recommend this method.

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.insertSheet('My New Sheet');

Where 'My New Sheet' is the name you want it to be.

Finegrained

Answer 3

Here is a simple example:

var name = (new Date()).toLocaleDateString();
SpreadsheetApp.getActiveSpreadsheet().insertSheet(name);

Phebe

Answer 4

Here's the way I did it...

var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = activeSpreadsheet.insertSheet();
newSheet.setName("whatever");

Walczak

How's that better than the earlier answers? – Affinal

Answer 5

I have been looking at the answers and although they answer the question of how to create a sheet and give it a name, none shows how to make that name variable by passing it as a parameter

This is an example of a function passing a variable to the createNewSheet function:

(The function that has to be executed is setNameAndCreateSheet)

function createNewSheet(sheetName){
  // The sheetName parameter has to be passed to the function when it's called
  let activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let newSheet = activeSpreadsheet.insertSheet();
  newSheet.setName(sheetName); // We sheet will be called as the string of the parameter
}

function setNameAndCreateSheet(){
  // This will get the email of the user executing the script
  const userEmail = Session.getEffectiveUser().getEmail();

  // This will get the current date
  const date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy");

  // We are making the string that will be passed to the createNewSheet function
  const sheetName = date + " - " + userEmail;
  // sheetName will return something like: "04/11/2021 - [email protected]"

  // This will execute the function that creates the sheet, and the name will be settled to the parameter passed (sheetName)
  createNewSheet(sheetName);
}

In this example, the parameter passed to the createNewSheet function is the concatenation of these two strings (date - useremail), but it could be anything.

Liftoff

Answer 6

This function does that with more options, where you can select:

1. The new sheet name
2. Add the sheet to which spreadsheet
3. After (or before) which sheet (index or name)
4. Choose to add a counter or delete if the new name is already used before.

---

function addSheet(shName, ss, aftr, bfr, delMode) {
  var sh = null;
  // Define the optional argument ss (Spreadsheet) if missing
  if (ss == null) ss = SpreadsheetApp.getActiveSpreadsheet();
  // If no new name is given, use "Sheet" and deactivate delete mode
  if (shName == null) {
    delMode = null
    shName = "Sheet"
  }
  
  if (delMode == null) {
    // If delete mode is inactive, use a counter if the name already exists (like "Sheet (2)")
    var n = 1;
    var nShName = shName;
    while (ss.getSheetByName(nShName) != null) {
      n += 1;
      nShName = shName + ' (' + n + ')';
    }
    shName = nShName;
  } else {
    // otherwise, delete on sight!
    sh = ss.getSheetByName(shName);
    if (sh != null) ss.deleteSheet(sh);
  }
  // Where to add the new sheet:
  if (aftr == null) {
    if (bfr == null) {
      // If both after and before arguments are missing, add the new sheet to the end of sheets list.
      n = ss.getSheets().length;
      // Uncomment the next one if you want it to be added after the active sheet
      // n = ss.getActiveSheet().getIndex();
    } else if (typeof(bfr) == 'string') {
      // If before argument was given as string (Sheet name), try to find it.
      n = getSheetOrder(bfr) - 1
    } else {
      // otherwise use the given number
      n = bfr - 1
    }
  } else if (typeof(aftr) == 'string') {
    // If after argument was give as string (Sheet name), try to find it.
    n = getSheetOrder(aftr);
  } else {
    // otherwise use the given number
    n = aftr;
  }
  // Note: if both after and bfr are given, bfr is ignored.

  // Fix the case where only before argument is gevin as a string,but no sheet with that name is found
  if (n < 0) n = 0;
  // Fix the case where the numbers given were too high
  if (n > ss.getSheets().length) n = ss.getSheets().length;
  // Do the action!
  return ss.insertSheet(shName, n);
}

---

function getSheetOrder(shName, ss) {
  // returns 0 if sheet is missing, or the sheet index if found.
  if (ss == null) ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(shName);
  if (sh == null) return 0;
  return sh.getIndex();
}

---

Tests:

addSheet() adds a sheet named "Sheet" (or "Sheet (2)", "Sheet (3)",...) to the end of the file (or after the active sheet if you edit the code)

addSheet("My Sheet", null, 3, null, 1) adds a sheet names "My Sheet" after the third sheet in the active spreadsheet, and deletes the old sheet with the same name if found.

Rebate