-1

I have written a script in Google Sheets to return the name of a given sheet (see below).

The function is written into a summary sheet, where the input given as the index of the sheet required. I have all the functions pre-written e.g.

A1 = GetSheet(1) B1 = GetSheet(2) C1 = GetSheet(3) ...etc

Obviously, when the sheet with the index in question does not yet exist, I get an error. However, when I do create that sheet, the cell does not automatically update with the name of the new sheet, it still returns an error. To get the name to appear, I have to delete the function and re-paste it in.

Is there a way for the cells to automatically update when a new sheet is added, without re-typing the function?

Apologies if I am missing something obvious, I am very new to this!

function GetSheet(input)
{
  var spread = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var sourceIndex = sourceSheet.getIndex();
  var nextIndex = sourceIndex + 1;  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(ss.getSheets()[input]);        
  var active = ss.getActiveSheet();
  var name = active.getName();                                   
  return name
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Owen Midha
  • 1
  • 3
  • 1
    The first 4 lines of your function don't seem to do anything to generate your return value. Also, take a look at this: spreadsheet `change` event (https://developers.google.com/apps-script/guides/triggers/events#change) to capture changes & trigger script to update. Further, why not get the names of all of the sheets first & then paste them into your summary sheet using `range.setValues()` (https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues) – Dean Ransevycz Nov 13 '17 at 21:48
  • @DeanRansevycz Thanks for noticing first 4 lines, I shall remove. From an old version. I'll take a look at `change`. The problem with finding sheet names first is I won't know what they are until they are created! – Owen Midha Nov 15 '17 at 17:44

1 Answers1

0

Not Changing because their inputs are not changing

The reason that your custom functions are not returning anything when you create another sheet is because the input to these kinds of function are generally expected to be cell references and the functions will recalculate when the value of those cell references change. But your inputs are just numbers (indexes to the allSheets() array and creating another sheets doesn't change any of their inputs so they never get a chance to run. I don't think that creating a new sheet will trigger an onChange I think these are the only things that trigger an onChange event.

EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT and OTHER

Admittedly, I don't know what OTHER is so maybe there's hope.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you, that makes a lot of sense. I added the random number change from [here](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet) and that is now refreshing my function – Owen Midha Nov 16 '17 at 11:22