# I need to perform the same function for each row in my spreadsheet. I have 30 rows in total

I am attempting to calculate and store the number of days absent for my employees - I can do it for one row (employee) but I have thirty employees:

function updateDaysAbsent() {

var formSS = ss.getSheetByName("TRANSPORT"); //Form Sheet
var tValA = [[formSS.getRange("G4").getValue()]]; // Result 4 days absent
var aValA = [[formSS.getRange("E4").getValue()]]; // Result 1 more day absent (always either 1 or Zero)

if(aValA == 1) {
var snValA = formSS.getRange("G4"); // Old number of days absent 4
snValA.setValue(Number(tValA)+1); // Result 5 days absents
}

var tAmt = formSS.getRange("B4"); // Cell that Changes aValA to 1 if entry is made
tAmt.setValue(""); // Reset to default absent at the end of everyday until entry made

}
• Hi David! Please take the tour here. It would be helpful if you could provide a sample sheet of what your data will look like. Below I have an answer, but it's merely a guess. Jun 8, 2022 at 1:47
• Hi, welcome to WebApps. David did you resolve your problem or are you still looking for help? Jun 25, 2022 at 8:42

I don't fully understand what your code does since I don't know what your spreadsheet looks like. This answer is basically a guess.

function updateDaysAbsent() {
const formSS = ss.getSheetByName("TRANSPORT"); //Form Sheet
const snValA = formSS.getRange("G4:G34"); // Old number of days absent 4
const tValA = snValA.getValues(); // Result 4 days absent
const aValA = formSS.getRange("E4:E34").getValues(); // Result 1 more day absent (always either 1 or Zero)
const replacementArray = new Array(30);
const blankArray = new Array(30);

for (let i = 0; i < 30; i++) {
if (aValA[i][0] == 1) {
replacementArray[i][0] = tValA[i][0] + 1; // Result 5 days absents
}
blankArray[i] = new Array(1);
}
snValA.setValues(replacementArray);
formSS.getRange(`B4:B34`).setValues(blankArray);
// Cell that Changes aValA to 1 if entry is made
// Reset to default absent at the end of everyday until entry made
}
• You are creating 2D arrays in tValA and aValA, but then immediately coercing the arrays to numbers, which seems confusing. The code uses Range.getValue() and Range.setValue() 60 times each, when one Range.getValues() and one Range.setValues() would suffice. See Apps Script best practices. Jun 8, 2022 at 6:43
• Ah that's true. Didn't put too much thought. I'll edit my answers to take those into consideration. Jun 8, 2022 at 8:28