CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Kasim Usman
Kasim Usman

Reputation: 57

Google Apps Script - how to applies this code for multiple sheets in google sheets

this code applies for just one sheet in a google sheet, how can i use to get four character in other sheets too.

function truncate() {
    const R        = SpreadsheetApp.openById("SheetId");
    const Choices  = R.getSheetByName("Choices");
    const Hrange = Choices.getRange("H2:H");
    const data    = Hrange.getValues().flat().map(r => [r.toString().substring(0,4)] );
    Hrange.setValues(data);
}

Upvotes: 1

Views: 135

Answers (1)

Marios
Marios

Reputation: 27390

You need to use getSheets() or getSheetByName() to get all sheets or selected sheets respectively. Either way, you need to iterate over all the selected sheets and one way to do that is to use forEach().

Solution for all sheets:

function truncate() {
  const R = SpreadsheetApp.openById("SheetId");
  const sheets = R.getSheets(); // for all sheets
  sheets.forEach(sh=>{
    var Hrange = sh.getRange("H2:H");
    var data = Hrange.getValues().flat().map(r => [r.toString().substring(0,4)] );
    Hrange.setValues(data);
    });
}

Solution for selected sheets by name:

function truncate() {
  const R = SpreadsheetApp.openById("SheetId");
  const sheets = ['Choices','Copy of Choices'].map(ch=>R.getSheetByName(ch)); // for selected sheets based on name
  sheets.forEach(sh=>{
    var Hrange = sh.getRange("H2:H");
    var data = Hrange.getValues().flat().map(r => [r.toString().substring(0,4)] );
    Hrange.setValues(data);
    });
}

In this solution please modify ['Choices','Copy of Choices'] to use the sheet names of your choice.

Upvotes: 1

Related Questions