CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Roberto Junior
Roberto Junior

Reputation: 125

Google Script + Spreadsheet + Convert entire sheet to uppercase on open

I´d like to know how i convert my entire spreadsheet to uppercase, on every load

I did it onEdit, but only the cell i edit

function onEdit(e) {
  if (typeof e.value != 'object') {
    e.range.setValue(e.value.toUpperCase());
  }
}

Thanks!!

Upvotes: 1

Views: 719

Answers (2)

user11982798
user11982798

Reputation: 1908

function ScriptOnLoad()
{
  var spreadsheet = SpreadsheetApp.getActive();
  var mysheet=spreadsheet.getActiveSheet();

  if (mysheet.getSheetName()!='SheetB') return;
  var rowCount=mysheet.getLastRow();
  var colCount=mysheet.getLastColumn();

  for (row=1; row<rowCount+1;row++)
  {
      for (col=1; col<colCount+1;col++)
      {
        var myFormula=mysheet.getRange(row, col).getFormula();
        if (myFormula!="")
        {
          //Make as comment if formula will let as is
          mysheet.getRange(row, col).setFormula( myFormula.toUpperCase()  );
        }
        else
        {
          var myFormula=mysheet.getRange(row, col).getValue();
          try 
          {
             mysheet.getRange(row, col).setValue( myFormula.toUpperCase() );
          }
          catch(err) 
          {
          }
        }
      }
  }
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201438

  • You want to convert all values in all cells in the Spreadsheet to the uppercase.
  • You want to run the script when the Spreadsheet is opened.

If my understanding is correct, how about this sample script? Please think of this as just one of several answers.

Sample script:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  sheets.forEach(function(sheet) {
    var range = sheet.getDataRange();
    var values = range.getValues().map(function(row) {return row.map(function(col) {return typeof col == "string" ? col.toUpperCase() : col})});
    range.setValues(values);
  });
}
  • In this function, the simple trigger is used. So when the Spreadsheet is opened, onOpen() is run by the OnOpen event trigger.
  • In this sample script, the event object is not used. So you can directly run the function of onOpen() with the script editor.

Note:

  • When the script is run, all values of all cells are converted. So when you test it, please be careful this. I recommend to prepare a sample Spreadsheet.

  • If you want to convert the values in the specific sheet, how about the following script?

    function onOpen() {
  var sheetName = "###"; // Please set the sheet name here.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues().map(function(row) {return row.map(function(col) {return typeof col == "string" ? col.toUpperCase() : col})});
  range.setValues(values);
}

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 2

Related Questions