CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
A Neal
A Neal

Reputation: 13

How to Use a Google Script to Change Contents of a Cell When the Sheet Name is Changed by the User

I am trying to allow users to change the name of a sheet; however, when they do I want the new sheet name to appear in a cell. I basically want the logic to be:

If a specific sheet's sheetname is edited {
  cell on another sheet = new sheet name
}

I can't seem to figure out how to use onEdit(e) to do this.

Any help would be greatly appreciated

Upvotes: 1

Views: 148

Answers (1)

Kristkun
Kristkun

Reputation: 5953

You can use installable change trigger which runs when a user modifies the structure of a spreadsheet.

Sample Code:

function onOpen(e){
  var ss = e.source;

  //Save the sheet id and sheet name in script properties
  var scriptProperties = PropertiesService.getScriptProperties();
  //Initially delete existing script properties
  scriptProperties.deleteAllProperties();
  var sheets = ss.getSheets();
  sheets.forEach(sheet => {
    Logger.log("id: "+sheet.getSheetId().toString()+", name: "+sheet.getName());
    scriptProperties.setProperty(sheet.getSheetId().toString(),sheet.getName());
  });
}

function onChange(e) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  Logger.log(e.changeType);
  if(e.changeType=="OTHER"){
    var scriptProperties = PropertiesService.getScriptProperties();
    var origName = scriptProperties.getProperty(sheet.getSheetId().toString());
    Logger.log(origName);
    Logger.log(sheet.getName());
    if(origName != sheet.getName()){
      //update script properties
      scriptProperties.setProperty(sheet.getSheetId().toString(),sheet.getName());
      //write new name in the cell
      sheet.getRange("A1").setValue(sheet.getName());

      Logger.log(scriptProperties.getProperties());
    }
  }
  
}

What it does?

  1. When the sheet was opened. Create a key-value pair based on sheet's id and the sheet's name. Save it in the script properties using PropertiesService
  2. Create an installable onChange trigger.
  3. Get the active sheet's id and name, get the original name based on the sheet's id in the script properties using PropertiesService.getProperty(key)
  4. Compare original sheet name and the current sheet name. If they are not the same, update the sheet name in the script properties using PropertiesService.setProperty(key,value). Then select your range and set the cell value using Range.setValue(value)

Output:

enter image description here

Apr 10, 2021, 4:28:46 AM    Info    OTHER
Apr 10, 2021, 4:28:46 AM    Info    Sheet1
Apr 10, 2021, 4:28:46 AM    Info    test
Apr 10, 2021, 4:28:46 AM    Info    {1029143107=Checkbox, 1793152938=test}

Note:

  • You can remove the deletion of existing script properties in onOpen() if you want.
  • You can change the range/cell where you want to write the new modified sheet name.

Upvotes: 2

Related Questions