Google Scripts and JavaScript for Google Sheets

Hi there,

I’ve been doing the introduction to JavaScript course I’ve not completed it yet but I have a good understanding so far. I have a really good knowledge of Google Sheets so I wanted to practice what I had learnt in a small task.

The task is: I have one spreadsheet with multiple sheets. Within each sheet I have a value in the cell that I want to use for the sheet name. So what I am trying to do it to call the sheet, and then call the value in that sheet and then overwrite the sheet name with the value. So whenever that cell is edited, it will automatically rename the cell.

I haven’t worked out how to handle all of the renaming of sheets at once but I’m trying one by one. When I run the code, it does execute and if I change the cell value it does execute and overwrite the sheet name momentarily, but it seems to then overwrite this again and it renames the sheet to “Range”.

I have tried everything and I am sort of getting to the conclusion that there is a loop or something in my script that is causing this issue. Can someone please take a look and help me to resolve this? It would be mightily appreciated! The code I am using is below:

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; //Sheet 1
var cell = sheet.getRange(“B1”);//get sheet and cell value
var value = cell.getValue();//Added
var oldName = sheet.getName();
sheet.setName(value);//Sheet name changed
if (cell.toString().length>0 && cell !== oldName) {
sheet.setName(cell);
}
}

In case anyone is wondering I have fixed it myself. I think it was because I was not defining a variable after getting the range from cell so it was literally pulling in the type not the value.

I have created an extra variable called cvalue and inputted this after the cell variable.

Also if you’re using sheets and in the scenario where you need this to action on every sheet to rename, providing the cell location is the same on every sheet, then you do not have to copy the function. I just changed the getActiveSpreadSheet to getActiveSheet and replaced all sheet references with the ss variable and it works:

function onEdit() {
var ss = SpreadsheetApp.getActiveSheet();
var range = ss.getRange(“B1”); //gets the cell value you want to rename the sheet with (change it to the value in the spreadsheet you want to change)
var cell = range.getCell(1,1); // gets the range (you do not need to change this because it will be like this every time)
var cvalue = cell.getValue();//Added
var oldName = ss.getName();
ss.setName(cvalue);//Sheet name changed
if (cvalue.toString().length>0 && cvalue !== oldName) {
ss.setName(cvalue);
}
}

Hope this helps someone out there as it was bugging me for ages!