if (e.changeType == "INSERT_GRID")... Undefined local variables

Question

In the following code it tells me inside the function that the local variables are not defined, when it worked before.

I'm looking to be able to debug the code so that it works again, when a trigger is activated.

function changeSheet(e) {
  
  if (e.changeType == "INSERT_GRID") {
    // list your sheet names in here:
  var sheetNames = ["Principal", "Lunes", "Martes", "Miércoles", "Jueves", "Viernes", "Lunes_blanco", "Martes_blanco", "Miércoles_blanco", "Jueves_blanco", "Viernes_blanco", "DATOS", "Automata"];
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

    if (sheets.length != sheetNames.length) {
      for (var i = 0; i < sheets.length; i++) {
        if (sheetNames.includes(sheets[i].getName())) {
          continue;
        }
        else {
          SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheets[i])
        }
      }          
    }
  }

During debugging the following message appears:

changeSheet @ debugged-code 22

Variables 

Local
  e: undefined
  sheetNames: undefinded
  sheets: undefined
  i: undefined

If you are running this from script editor `e` is undefined so everything beyond that will also be undefined. This will only work from a simple or installed trigger and when it is triggered. — TheWizEd, Jan 10 '23 at 15:41

score 0 · Answer 1 · answered Jan 10 '23 at 09:53

The script you quote apparently attempts to prevent the user from creating tabs whose name is not in the list.

The function is designed to run automatically on an installable trigger. In that context, the event object e is properly populated. Do not run the code in the script editor. If you do, the event parameter e is not populated, causing the error you mention.

Try something like this to make it work:

/**
* Deletes sheets that do not match sheetsToKeepRegex.
* In effect, prevents the user from inserting new sheets.
*
* Installable trigger that runs each time the spreadsheet changes.
* To create a trigger to run the function, see:
* https://developers.google.com/apps-script/guides/triggers/installable
*
* @param {Object} e The 'on change' event object.
*/
function onInsertSheet(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onInsertSheet(e) function in the script editor window. '
      + 'It runs automatically when you edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  if (e.changeType !== 'INSERT_GRID') {
    return;
  }
  const sheetsToKeepRegex = /^(Principal|Lunes|Martes|Miércoles|Jueves|Viernes|Lunes_blanco|Martes_blanco|Miércoles_blanco|Jueves_blanco|Viernes_blanco|DATOS|Automata)$/i;
  const deletedSheetNames = [];
  const ss = SpreadsheetApp.getActive();
  ss.getSheets().forEach(sheet => {
    const sheetName = sheet.getName();
    if (!sheetName.match(sheetsToKeepRegex)) {
      if (ss.getSheets().length === 1) {
        ss.toast('Cannot delete last sheet in the spreadsheet. To undo, choose Edit > Undo.', 'onInsertSheet function', 30);
        return;
      }
      ss.deleteSheet(sheet);
      deletedSheetNames.push(sheetName);
    }
  });
  if (deletedSheetNames.length) {
    ss.toast(`Deleted ${deletedSheetNames.length} sheet(s): ${deletedSheetNames.join(', ')}. To undo, choose Edit > Undo.`, 'onInsertSheet function', 30);
  }
}

if (e.changeType == "INSERT_GRID")... Undefined local variables

1 Answers1