How to set column to plain text format for every sheet?

Question

I've read through this post >> set format as plain text

I'm creating a new thread because that thread is a few years old already and I didn't want to revive something from a few years ago.

the code:

function A1format() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var mainsheet = ss.getSheetByName("Sheet1");
 var G = mainsheet.getRange("C15:BH3000").getGridId();
 var illa = mainsheet.getRange("A13");

 Logger.log(G);
 illa.copyFormatToRange(G, 16, 3,200, 30);
 }

This code is supposed to set plain text format for the sheet named Sheet1

I've tried var mainsheet = ss.getSheetByName("Sheet1, Sheet2, Sheet3"); but this doesn't seem to work, I just get an error message.

This is the current code I have, this code works but is both inefficient and a real pain to maintain if something changes:

function setPlainTextDefault() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheets()[0];
  var sheet2 = ss.getSheets()[2];
  var sheet3 = ss.getSheets()[4];
  var sheet4 = ss.getSheets()[7];


  var sheetColumn1 = sheet1.getRange("A1:A");
  var sheetColumn2 = sheet2.getRange("A1:A");
  var sheetColumn3 = sheet3.getRange("A1:A");
  var sheetColumn4 = sheet4.getRange("A1:A");
  sheetColumn1.setNumberFormat("@");  
  sheetColumn2.setNumberFormat("@");
  sheetColumn3.setNumberFormat("@");  
  sheetColumn4.setNumberFormat("@");
}

Here I am changing each column A in every sheet to plain text by using the index number of the sheet, so I have to manually count the number for every sheet, this is a nightmare as I have a very large number of sheets, it will take too much time for me to manually count the sheets and then add it to my current code. I know there is a better more efficient way of doing this, but I don't know how due to my lack of knowledge in google apps scripting.

How do you do this for every sheet in the document regardless of how many sheets are present? I want to go through every sheet, from sheet1 till x number of sheets and then change every column A to plain text.

score 1 · Answer 1 · edited Apr 07 '19 at 07:33

var mainsheet = ss.getSheetByName("Sheet1, Sheet2, Sheet3") throws an error because there isn't a sheet named "Sheet1, Sheet2, Sheet3". "There isn't a way to reduce the calls to the Spreadsheet Service on your code, but you could make that it "looks better" by using some JavaScript features like loops and array handling.

Example (untested)

On the following code snippet, arrays, a for and indexOf are used to reduce a bit the number of lines of the original code.

function setPlainTextDefault() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheetsToProcess = [0,2,4,7]; // Array holding the sheets indexes to process
  for(var i = 0; i < sheets.length; i++){
    /* If i is not in the sheetsToProccess indexOf returns -1 which is parsed as false 
    otherwise the result is parsed as true */
    if(sheetsToProcess.indexOf(i)){ 
     var sheet = ss.getSheets()[i]; 
     var sheetColumn = sheet.getRange("A1:A");
     sheetColumn.setNumberFormat("@");
    }      
  }
}

To make the above work for every sheet, comment out or remove if(sheetsToProcess.indexOf(i)){ and the corresponding }.

It's worth to note that if you are looking help to find the "best" way to improve your code, you could try Code Review.

Thank you for the pointers Sir, I've took your example code and answered my own question below. — Shawn Law, Jan 11 '18 at 04:54

score 0 · Accepted Answer · edited Apr 07 '19 at 07:33

This was the code I needed:

function setPlainText() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();

   for(var i = 0; i < sheets.length; i++) {

     //Logger.log(sheets[i].getName());
     var setPlainText = ss.getSheets()[i]; 
     var sheetColumnA = setPlainText.getRange("A1:A");
     sheetColumnA.setNumberFormat("@");

     var sheetColumnB = setPlainText.getRange("B1:B");
     sheetColumnB.setNumberFormat("@");

   }
}

It will:

Iterate through a document with x number of sheets however big or small
Then for each iteration, set plain text format for every column A and B for every sheet in the document

How to set column to plain text format for every sheet?

2 Answers2