Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created Feb 15, 2022
Embed
What would you like to do?
Counter in Cell of Google Spreadsheet using Infinite Loop with Google Apps Script

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment