CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Léo Davesne
Léo Davesne

Reputation: 2251

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

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.

Upvotes: 62

Views: 143719

Answers (6)

Abdallah El-Yaddak
Abdallah El-Yaddak

Reputation: 466

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.

Upvotes: 0

Nicolas Domergue
Nicolas Domergue

Reputation: 113

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.

Upvotes: 2

BadSushi
BadSushi

Reputation: 233

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.

Upvotes: 23

Jaystew
Jaystew

Reputation: 299

Here's the way I did it...

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

Upvotes: 7

Benoit
Benoit

Reputation: 486

Here is a simple example:

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

Upvotes: 20

L&#233;o Davesne
L&#233;o Davesne

Reputation: 2251

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.

Upvotes: 98

Related Questions