CODEWITHSUNDEEP
google-sheets
zerowords
zerowords

Reputation: 3113

Sheetname recognizing script fails after manually duplicating sheet

I have a sheet named First containing the following in cell A1 (see below). After I manually duplicate First during google spreadsheet development, I want the duplicated cell A1 value to be Second Round, but even after a refresh of the manually duplicated and renamed sheet Second, the value of cell A1 is Copy of First Round.

Can I alter the script or something to get my desired result, Second Round?

A1: =SHEETNAME()&" Round"

Script to access sheetname:

function sheetname() {
   var sheetname = SpreadsheetApp.getActiveSheet().getName();
          return sheetname;

}

Upvotes: 0

Views: 96

Answers (1)

Karl_S
Karl_S

Reputation: 3574

There needs to be some kind of trigger to cause the function to run. A custom function in a cell in the spreadsheet will only run if a cell in the range inside the () changes. You COULD set this to be a large range, as long as you don't include the cell the formula is in as this causes an error. Then you will get an update when a cell changes. Unfortunately this does not include the reference to the sheet name changing, such as Sheet1 in =getSheetname(Sheet1!B1:Z)

There are Triggers which happen in scripts, and can cause a given function to run. The onChange trigger has to be manually applied to a function, meaning in the script editor you set it under Edit->Current project's triggers. The probnlem, however, is that it will not update any cells unless you have code in the function to change specific cells. So you would not have a function in the cell, but this in the script editor, with A13 set to the cell where you want the sheet name. Once that is in and the Trigger is set, changing the sheet name will change the cell value.

function getSheetname() {
  var ss = SpreadsheetApp.getActiveSheet();
  var sheetname = ss.getName();
  ss.getRange('A13').setValue(sheetname);
  return;
}

Upvotes: 1

Related Questions