Google Sheets batch rename files with a script?-CodePudding

I have a lot of numbers in my sheet and I would like to rename over 100 different files. Example, the number in my sheet with the number 1 I want to rename it to 01.png. I`ve tried already the search and replace function, but it is very time consuming, if I want to do it multiple times on x sheets... Gives here a better and quicker solution?

I've tried already the replace function:

=REPLACE(A1;1;3;"_01.PNG")

It gives me the right output file name, but I have to select thousands of a number 1. How can I select all Number 1 (formatted in text) and set this function?

CodePudding user response：

Customized Code, it only changed me the names from the first column (A). What I have to change here? Thanks.

function rename() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets(); 
  
  sh.forEach(sheet => { 
    var range = sheet.getRange("A2:F301"); //get all the cells from my sheet
    var data = range.getValues(); 
    for(var i = 0; i < data.length; i  ) 
    {
      data[i][0] = "_"   pad(data[i][0])   ".PNG" //replace each data with "_NUMBER.PNG"
    }
    range.setValues(data); 
  })

}

function pad(n) {
    return (n < 10) ? ("0"   n) : n;
}

CodePudding user response：

EDIT:

Try this:

function rename() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()); //this will automatically select all data in your active sheet starting from row 2
  var data = range.getValues(); //get the values, this will return 2D array

  for(var i = 0; i < data.length; i  ) {
    for(var j = 0; j < data[0].length; j  ){
      data[i][j] = "_"   pad(data[i][j])   ".PNG" //replace each data with "_NUMBER.PNG"
    }
  }
  range.setValues(data);
}

function pad(n) {
    return (n < 10) ? ("0"   n) : n;
}

Sample Data:

Output:

References:

CodePudding user response：

My sheet image

Here my sheet, I do not need it for all sheets. Just for the whole active sheet, it should be doesn`t matter how many rows it has. Can anyone help?