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
});
}
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
}
}
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.
insert the entry? Do you want to write all these values to D2:D, waiting 3 seconds between the writing of each cell?
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.
}
getRange(2,1,sheet.getLastRow()-1).getValues()or as some dogetRange('A2:A' + sheet.getLastRow()).getValues()not providing a numerical value to the last part often generates null all the way down to maxrows. Some people filter it out with.filter(e=>return e);which utilizes the falsyness of e