CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsrename
Nicole Sophie
Nicole Sophie

Reputation: 25

Automatically rename Google Spreadsheet

function tabellenblattUmbenennen(){
 var oldSpreadsheetName="Perso2";
 var newSpreadsheetName="C7";
 SpreadsheetApp.getActiveSpreadsheet().getSheetByName(oldSpreadsheetName).setName(newSpreadsheetName); 
}

How do I have to change the script so that the name from table xy line C2 is used to rename the table. And the whole thing should go back too.

Or can you edit this script so that it can do the renaming automatically? Reference to the name in C2 then.

 // Wird bei Änderung einer Zelle automatisch ausgeführt
    function onEdit(e){ 

    // Aufrufen der Funktion zum Sortieren
      neueSortierung(); 

    } 

    // Funktion welche die Einträge sortiert 
    function neueSortierung(){ 

      // Auf das aktive Tabellendokument zugreifen
      var ss = SpreadsheetApp.getActiveSpreadsheet();

      // Auf das aktive Tabellenblatt zugreifen
      var sheet = ss.getActiveSheet();

      // Den Bereich der sortiert werden soll festlegen
      var range = sheet.getRange("A4:E999"); 

      // Den vorher sortierten Bereich nach Spalte 2 sortieren
      range.sort([ {column: 5, ascending: true}]);

    }

We currently have 25 tables assigned to 20 people. When people wake up the table name should change automatically.

Sorry for the bad translation. Since I cannot speak English myself, I had to do this via Google Translate.

Upvotes: 1

Views: 1314

Answers (1)

Mateo Randwolf
Mateo Randwolf

Reputation: 2930

Problem

I believe this is what you are trying to achieve from your explanation:

Change the name of a sheet depending on the dynamic value of a specific cell (in your case C2)

Solution

If the sheet is our active sheet

Here is the piece of code for achieving this. It has comments that will explain you each part of it:

function onEdit() {
  
  // get the active sheet of the spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // get the cell value even after been edited
  var cellValue = sheet.getRange('C2').getValue();
  
  // rename the sheet according to that value
  sheet.setName(cellValue);
}

If the sheet is a specific sheet we want to rename

Here is the piece of code for achieving this. It has comments that will explain you each part of it: 

function onEdit() {
  
  // get all the sheets in the active spreadsheet
  var sheets = SpreadsheetApp.getActive().getSheets();
  
  // go over every sheet and check if it matches the id of the one we want to change the name
  for (i=0;i<sheets.length;i++){
    
    // if the id matches select that as our sheet 
    if(sheets[i].getSheetId()=="0.0"){
      var sheet = sheets[i];
    }
    
  }  
  // get the cell value even after been edited
  var cellValue = sheet.getRange('C2').getValue();
  
  // rename the sheet according to that value
  sheet.setName(cellValue);
}

If you want to change the names of several sheets with respect to the cell values of the column of one sheet (as it is your specific case)

function onEdit() {
  
  // get all the sheets in the active spreadsheet
  var sheets = SpreadsheetApp.getActive().getSheets();
  
  // Get sheet containing all names (in this case the first sheet)
  var sheetWithNames = sheets[0];
  
  // go over every sheet and assign the name of the appropiate cell of the first sheet
  for (i=1;i<sheets.length;i++){
    sheets[i].setName(sheetWithNames.getRange(i+1, 2).getValue());  
  }  

}

I hope this has helped you. Let me know if you need anything else or if you did not understood something. Guten Abend! :)

Upvotes: 1

Related Questions