Make a simple counter in Google Scripts which keeps track of previous value

Question

so I'm trying to make an auto-rotating schedule using google sheets and a custom function which selects a new employee from a linked sheet each week and then starts again at the top of the list when it gets to the bottom, using a Google trigger to run the counter every 7 days.

I'm having a hard time figuring out how to store a value in the spreadsheet each week to store the counter's value and then refer to that same value when the function runs again to update the counter.

I'm also having an issue where my spreadsheet throws the 'result was not a number' error with my current output, likely because it's referring to itself and I can't figure out how to initialize the counter when it can only store the formula in the cell it refers to.

Here's what I have:

/* counter starts at 2, increases each week (called upon by Google trigger to run each week) until 
it reaches the lastRow of employees and then resets to two.
Returns this week's counter value each time to cell where function is called. */

function cleanerCounter(){ 
  /*sheets*/
  var sheet = SpreadsheetApp.getActive().getSheetByName('KitchenDuties');
  var eDirectory = SpreadsheetApp.getActive().getSheetByName('EmployeeDirectory');
  var lastRow = eDirectory.getLastRow(); //last row that contains an employee in the directory

   //counter setup
  var counter = sheet.getRange(6,2);
  counter = counter.getDisplayValue(); 
  counter = +counter; 

  if(counter >= lastRow){
    counter = 2;
    return +counter;
  } else {
    return +counter;
  }
}

I would store the counter in [PropertiesService](https://developers.google.com/apps-script/reference/properties/properties-service#getDocumentProperties()) — Cooper, Jun 16 '20 at 23:17
I've tried this, but it doesn't work because I think with PropertiesService it reinitializes the property each time the script is run and doesn't store it once it goes out of scope. For this instance I need the counter to be stored once the script terminates and then to be able to access the stored value again when the script is run again. — akajanedoe, Jun 17 '20 at 19:03

Cooper · Answer 1 · 2020-06-17T19:53:10.403

A Simple Properties Service Counter

function getCounter() {
  const ps=PropertiesService.getScriptProperties();
  var n=ps.getProperty('counter');
  if(!n){ 
    ps.setProperty('counter', 2);//initialize counter if not there
    var n=2;
    return n;
  }
  if(n>=SpreadsheetApp.openById("Insert your spreadsheet id").getSheetByName('EmployeeDirectory').getLastRow()) {
    ps.setProperty('counter',2)
  }else{
    ps.setProperty('counter',Number(n) + 1);
  }
  return ps.getProperty('counter');
}

score 0 · Accepted Answer · answered Jun 17 '20 at 20:03

I was wrong, PropertiesServices was definitely the way to go, thanks for the tip. I did something a bit different before you had a chance to respond and it works a bit better for my purposes:

function cleanerCounter(){ 
  /*sheets*/
  var sheet = SpreadsheetApp.getActive().getSheetByName('KitchenDuties');
  var eDirectory = SpreadsheetApp.getActive().getSheetByName('EmployeeDirectory');
  var lastRow = eDirectory.getLastRow(); //last row that contains an employee in the directory
  var documentProperties = PropertiesService.getDocumentProperties();

  var counter = documentProperties.getProperty('COUNTER');

  counter = parseInt(counter);
  counter++;
  counter = counter.toString();
  documentProperties.setProperty('COUNTER', counter);
  Logger.log('COUNTER =', documentProperties.getProperty('COUNTER'));

  var output = sheet.getRange(2, 6).setValue(parseInt(documentProperties.getProperty('COUNTER')));


}


function resetCounter(){
  var documentProperties = PropertiesService.getDocumentProperties();
  var counter = documentProperties.setProperty('COUNTER', '2');
}

Please note that `counter = counter.toString()` is not needed since `setProperty` does that for you. If you do that, you won't need the last line of the `cleanerCounter` as well (just return the counter). Best practice is to avoid variable reassigning (and definitely not change their data type - sounds counter-intuitive with JS being dynamically typed, but it will save you a lot of debugging time) — Oleg Valter is with Ukraine, Jun 18 '20 at 20:47

Make a simple counter in Google Scripts which keeps track of previous value

2 Answers2

Linked