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 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');
}