CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yvan L
Yvan L

Reputation: 333

Google Sheet + App script: How to loop for each with an array

Hi I'm trying to get a script that will run some code for each name on a list created from my 'sheet1' column 'A2:A'

1. get the list from Column A2:A
2. run code for each name from the list

Here's my code but it's not working properly I don't think the array is populating properly

function test(){
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1');
var list = sheet.getRange('A2:A').getValues();
var a = [list];
a.forEach(function(entry) {
    
  //do something

 });
}

Upvotes: 0

Views: 4073

Answers (3)

Cooper
Cooper

Reputation: 64032

function loopthroughnamesfoundinsheet(){
  const ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('sheet1');
  var list = sheet.getRange(2,1,sheet.getLastRow()).getValues().flat();//flattened array      
  list.forEach((name,i)=> { 
    let sh=ss.getSheetByName(name);//I added code here to show you how to write data to 'D2' in each sheet
    sh.getRange('D2').setValue(i+2);
    //another thing you might like to do is to copy a bunch of data all at once
    sh.getRange(2,2,list.length,list[0].length).setValues(list);//this copies the list in Sheet1 into every sheet in the list.
    console.log(name);
  });
  SpreadsheetApp.flush();//will force all pending calculations on the spread to complete and data will appear afters on the spreadsheet.
}

Upvotes: 0

Marios
Marios

Reputation: 27348

You can flatten the list array and then iterate over each value directly.

Code snippet:

function test(){
var sheet = SpreadsheetApp.getActive().getSheetByName('sheet1');
var list = sheet.getRange('A2:A'+sheet.getLastRow()).getValues().flat(); // modified code
list.forEach(entry=> { 
  //do something with entry
  console.log(entry);
 });
}

I use an arrow function but you can use your current solution as well.

Upvotes: 1

mxLiew
mxLiew

Reputation: 26

Sample Sheet

function test(){
    var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
    var lastSourceRow = sheet.getLastRow();//get last row
    for (i=2; i<=lastSourceRow; i++){//ignore headers and loop 
      var currentName=sheet.getRange(i, 1).getValue(); //looping column A 
      console.log(currentName)
      //process the name 
    }
    
}

Upvotes: 1

Related Questions