Google Sheets Script hideSheet() , Sheet not hidden

Question

Trying to duplicate a hidden template, causing it to be revealed, but then have it be hidden again after the duplication.

The hideSheet() function is not working. Any ideas?

function googleQuiz(){

//Duplicate Sheet 
  var ss =SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.setActiveSheet(ss.getSheetByName('QuizFormTPlate'));
  ss.duplicateActiveSheet();
  sheet.hideSheet();
//Rename sheet
  var dSheet = ss.setActiveSheet(ss.getSheetByName('Copy of QuizFormTPlate'));
  var date = new Date();
  var tz = ss.getSpreadsheetTimeZone();
  var marksDate = Utilities.formatDate(date, tz, 'dd-MMM');
  var name = "G-Quiz ".concat(marksDate);
  dSheet.setName(name);
//Insert Cell 
  var cell = dSheet.getRange("C2");
  cell.setValue('Formative');
}

Answer 1

Actually it's "showSheet()" This works for me:

var spreadsheet = SpreadsheetApp.getActive();
var sheetTemplate = spreadsheet.getSheetByName('Template');
sheetTemplate.showSheet();
//do something...
sheetTemplate.hideSheet();

Since hideSheet() takes no effect if the sheet is already hidden:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#hidesheet

Showing the hidden sheet first should do the trick:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#showsheet

Answer 2

Here is alternative solution that will that copy the sheet and keep it hidden

function googleQuiz(){

//Duplicate Sheet 
  var ss =SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('QuizFormTPlate');
  var dSheet = sheet.copyTo(SpreadsheetApp.openById(ss.getId()))
  dSheet.showSheet()
//Rename sheet
  var date = new Date();
  var tz = ss.getSpreadsheetTimeZone();
  var marksDate = Utilities.formatDate(date, tz, 'dd-MMM');
  var name = "G-Quiz ".concat(marksDate);
  dSheet.setName(name);
//Insert Cell 
  var cell = dSheet.getRange("C2");
  cell.setValue('Formative');
}

Basically, instead of your using duplicateActiveSheet() function of a spreadsheet object. You can use copyTo() function of a sheet object and provide it with the current spreadsheet. sheet.copyTo(SpreadsheetApp.openById(ss.getId())). And directly pass the new sheet object to var dSheet to make it visible and rename it.

Answer 3

It appears the issue is caused by using setActiveSpreadSheet on a hidden sheet, if you call ss.getSheetByName('QuizFormTPlate').show(); prior to the ss.setActiveSpreadsheet(ss.getSheetByName('QuizFormTPlate')); the call to hideSheet() will work fine afterwards. Posting this here so anyone else running into this problem can just add the .show() instead of reworking their whole code.