CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Evgeniy
Evgeniy

Reputation: 2605

How can i add list of sheets to Google App Script?

I have a script, which works in a tab, which is hardcoded in the line 4 (Sheet1):

function fillColumn() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const values = sh.getRange('A2:A'+sh.getLastRow()).getValues().flat([1]);

  var toCopy;
  values.forEach((el,index)=>{
    if (el != ''){
      toCopy = sh.getRange(index+2,1).getValue();
    } else {
      sh.getRange(index+2,1).setValue(toCopy)
    }
  })
}

What is the correct way to add a list of tabs, to make script working in tabs Sheet1, Sheet2, Sheet12? I tried some notations, but they were all erroneous.

Upvotes: 1

Views: 59

Answers (2)

Marios
Marios

Reputation: 27390

Explanation:

  • You need to loop over the sheets. There are multiple ways to do that, but since you are already familiar with the forEach loop, you can wrap your code with another forEach loop that iterates over the selected sheets.

  • You want to iteratively get and set values in multiple sheets, then it is a good idea to flush the changes at the end of each sheet iteration.

Solution:

function fillColumn() {
  const ss = SpreadsheetApp.getActive();
  ["Sheet1", "Sheet2", "Sheet12"]. // Add sheets here
  forEach(sn=>{ 
         let sh = ss.getSheetByName(sn);
         let values = sh.getRange('A2:A'+sh.getLastRow()).getValues().flat();                                                                               
         let toCopy;
         values.forEach((el,index)=>{
         if (el != ''){
                toCopy = sh.getRange(index+2,1).getValue();
         } else {
                sh.getRange(index+2,1).setValue(toCopy)
         }
         SpreadsheetApp.flush();
         });
   });   
}

Upvotes: 1

Martí
Martí

Reputation: 2861

You can simply iterate through ss.getSheets which returns an array of the sheets in the document:

for (let sheet of ss.getSheets()) {
 // Do something for each sheet
}

If you don’t want to iterate all of them, you can have an array of the names, iterate it, and use the name to get the sheet:

const sheetNames = [
 'Sheet 1',
 'Special sheet',
 'Sheet 25',
]
for (let name of sheetNames) {
 const sheet = ss.getSheetByName(name)
 // Do something with sheet
}

References

Upvotes: 1

Related Questions