Google Apps Script not properly updating

Question

I'm working on creating a system for other teachers to easily track their students' progress. I've got a spreadsheet with individual sheets for each student and then a sheet for an overview of all students. The spreadsheet has the following script attached to it:

function SheetNames() { 
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1) ;
  for (var i = 1 ; i < sheets.length ; i++ ) {
    out[i] = [sheets[i-1].getName()];
  }
  return out
}
catch( err ) {
  return "#ERROR!" 
}
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Student List')
      .addItem('Update Student List', 'SheetNames')
      .addToUi();
}

In the "Overview" sheet, I have a cell that just contains =SheetNames(). When I first enter the custom function, the list populates. When I open the spreadsheet, the menu is added as it should be. However, when I click the menu item, the list of students on the "Overview" sheet is not updated. Is there anyway to make this function automatically update?

You could add SpreadsheetApp.flush() to the SheetNames() function, at the bottom. Apps Script documentation — Alan Wells
@SandyGood Tried that, still no luck. I've also tried naming the function onEdit() to make it auto update on every edit, and I've tried adding in a time-based event trigger. I cannot get this thing to update automatically. — Donald Brown

Anton Dementiev Anton Dementiev · Accepted Answer · 2017-06-06T23:00:44

It will not update because all that function does is return an array. When you use the =SheetNames() notation, you are giving it a range to write to (the cell where you put the formula).

When you run the function via menu click, it doesn't know anything about the target range in the spreadsheet. It simply creates a variable ('out'), stores it in memory, then destroys it when the function finishes executing.

If you'd like to write to specific range, you should reference it in your function. Here's a quick example of the function that writes a random number from 0 to 100 to cell A1 of the first sheet

function populateCell() {

var number = Math.random() * 100;
var cell = SpreadsheetApp.getActiveSpreadsheet()
                         .getSheets()[0]
                         .getRange("A1");

cell.setValue(number);

}


function onOpen() {

SpreadsheetApp.getUi()
              .createMenu('menu')
              .addItem('run', 'populateCell')
              .addToUi();



}

It will not work as a custom function though. As per GAS documentation, if a function is being called from a cell (is custom), that function is read-only and can't set values https://developers.google.com/apps-script/guides/sheets/functions

Also, there's no need to use custom function because you seem to only need this code for specific range. If you intend to write to whatever cell the cursor is currently in, you should remove the reference to specific cell and use sheet.getActiveCell() instead. Only use custom functions for general functionality not tied to specific range.

Google Apps Script not properly updating

1 Answers