vendredi 27 août 2021

Increment cell value if another cell is below a certain value

Quick disclaimer: I'm completely noob when it comes to javascript.

I am currently working on a google sheet that is counting attendance.

I would like to add an automatic function, that is increasing the "Warnings" cell by one should a person's attendance is below a certain value (6) at the end of a month.

This is how the google sheet looks for better understanding: attendance

This is the code I have right now, which is working:

     function increment() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets()[0];

   var range = sheet.getRange('G4');
   //Returns the value of the top-left cell in the sheet
   var value = range.getValue();

  if (sheet.getRange('D4')  == "1","2","3","4","5") {
    range.setValue(value + 1);
 }
  }

My main issue is, that obviously this only works for the G4/D4 cells. I have a total of 48 cells where I'd need to run this script with a trigger. Simply giving range doesn't work as it simply checks if any of the D column cells are below the value (or meets the 5 values I've added) and will just add one to every G column cell, even if one of the D cells are actually not meeting the criteria.

Is there any way to handle this within a single script, instead of having to remake this script 47 more times and setting 48 different triggers?

Aucun commentaire:

Enregistrer un commentaire