0

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 ss = SpreadsheetApp.getActiveSpreadsheet();
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
  
}
2
  • 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.
    – David Tan
    Jun 8 at 1:47
  • Hi, welcome to WebApps. David did you resolve your problem or are you still looking for help?
    – Tedinoz
    Jun 25 at 8:42

1 Answer 1

0

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 ss = SpreadsheetApp.getActiveSpreadsheet();
  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
}
2
  • 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 at 6:43
  • Ah that's true. Didn't put too much thought. I'll edit my answers to take those into consideration.
    – David Tan
    Jun 8 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.