Counter in Cell of Google Spreadsheet using Infinite Loop with Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script of a counter in a cell of Google Spreadsheet using the infinite loop with Google Apps Script. Recently, I have reported about the infinite loop on Google Spreadsheet. Ref This sample script achieves a counter in a cell using the infinite loop.

Sample script

This sample script is a test script for counting the number using the infinite loop. Please be careful this. Please copy and paste the following script to the script editor of Google Spreadsheet and save it. And, please install OnChange trigger to the function onChange().

And, please put a custom function of =sample(). By this, the infinite loop is started as showing in the above demonstration.

function sample() {
  const p = PropertiesService.getScriptProperties();
  const pv = p.getProperty("count");
  const c = pv ? Number(pv) + 1 : 1;
  p.setProperty("count", c);
  return c;
}

function onChange(e) {
  var lock = LockService.getDocumentLock();
  if (lock.tryLock(60000)) {
    try {
      Utilities.sleep(1000);
      const formula = "=sample";
      const tempFormula = "=sampleFormula";
      [
        [formula, tempFormula],
        [tempFormula, formula],
      ].forEach(([a, b]) =>
        e.source
          .createTextFinder("^\\" + a)
          .matchFormulaText(true)
          .useRegularExpression(true)
          .replaceAllWith(b)
      );
    } catch (e) {
      throw new Error(e.message);
    } finally {
      lock.releaseLock();
    }
  }
}

Reference

 Share!