在Google脚本中创建一个简单的计数器，以跟踪先前的值 [英] Make a simple counter in Google Scripts which keeps track of previous value

问题描述

所以我正在尝试使用Google工作表和自定义功能制定一个自动轮换时间表，该功能每周会从链接工作表中选择一个新员工，然后在到达底部时再次从列表的顶部开始，使用Google触发器每7天运行一次计数器.

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.

这就是我所拥有的:

/* 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;
  }
}

推荐答案

我错了，PropertiesServices绝对是必经之路，感谢提示.在您有机会回应之前，我做了一些与众不同的事情，它对我而言效果更好:

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

这篇关于在Google脚本中创建一个简单的计数器，以跟踪先前的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！