CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Michael Courcy
Michael Courcy

Reputation: 637

Spreadsheet().insertSheet(index, name) does not insert the sheet at index but as the last sheet

When I execute this code I expect the Generated sheet to be inserted right after the active sheet. But that's not the case, it's always created as the last sheet.

Let's say I have 2 sheets : activeSheet and notActiveSheet and I run this code:

  var indexActiveSheet =  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getIndex()
  var nameActiveSheet =  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName()
  Logger.log(indexActiveSheet,nameActiveSheet)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(GENERATED);
  if  (sheet == null){
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(GENERATED, 1 + indexActiveSheet)    
  }
  Logger.log(sheet.getIndex(), sheet.getName())

But the logs and the spreadsheet shows that's not the case :

[20-10-18 14:42:17:468 CEST] 1.0 activeSheet
[20-10-18 14:42:17:811 CEST] 3.0 Generated

I was expecting

[20-10-18 14:42:17:468 CEST] 1.0 activeSheet
[20-10-18 14:42:17:811 CEST] 2.0 Generated

Upvotes: 2

Views: 1616

Answers (1)

Marios
Marios

Reputation: 27390

Issue / Explanation:

If you see the official documentation for insertSheet:

The index of the newly created sheet. To insert a sheet as the first one in the spreadsheet, set it to 0.

  • Namely, if you want to place the Generated sheet after the activeSheet you need to call:

    insertSheet(GENERATED, indexActiveSheet)

  • For example, in your case, indexActiveSheet is 1. To place the Generated sheet to the second position, you also need 1 because this is what insertSheet accepts if you want to place it at the second position. Remember, in insertSheet(sheetName, sheetIndex) sheetIndex=0 is the first sheet, sheetIndex=1 is the second sheet.

Solution:

function myFunction() {
  
  var GENERATED = 'Generated';
  var indexActiveSheet =  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getIndex()
  var nameActiveSheet =  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName()
  Logger.log(indexActiveSheet,nameActiveSheet)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(GENERATED);
  if  (sheet == null){
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(GENERATED, indexActiveSheet)    
  }
  Logger.log(sheet.getIndex(), sheet.getName())
  
}

Improvements (optional):

You don't need to call SpreadsheetApp.getActiveSpreadsheet so many times when you can simply store into a variable and use that variable instead:

function myFunction() {
  
  var GENERATED = 'Generated';
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var act_sh = ss.getActiveSheet();
  var indexActiveSheet =  act_sh.getIndex()
  var nameActiveSheet =  act_sh.getName()
  var sheet = ss.getSheetByName(GENERATED);
  if  (sheet == null){
    sheet = ss.insertSheet(GENERATED, indexActiveSheet)    
  }
}

Upvotes: 2

Related Questions