Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created May 22, 2019 01:37
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save tanaikech/52e7bbdabf8bfc34ac16d5f27fd8cb80 to your computer and use it in GitHub Desktop.
Possibility of Real Time Processes In a Cell on Spreadsheet using Google Apps Script

Possibility of Real Time Processes In a Cell on Spreadsheet using Google Apps Script

This is a sample script for investigating the possibility of the real time processes in a cell on Google Spreadsheet using Google Apps Script. As a sample situation, it tried the real time clock in a cell on Google Spreadsheet using Google Apps Script.

Demo:

Usage:

When you use this script, please do the following flow.

  1. Copy and paste the following script to the script editor (the container-bound script of Spreadsheet).
  2. Run the function of run().
    • By this, a sidebar is opened on the Spreadsheet.
  3. When you click a button of "start", the clock is started at the cell "A1" of the active sheet.
    • In this sample script, the value is updated every 0.2 seconds. But the process speed of Google Apps Script is often changed even if the same script is run. So when you see the demonstration, there are sometimes the cases of 2 second step.
    • If you want to stop the clock, please click "stop" button.

Sample script:

function runCLock() {
  Utilities.sleep(200);
  var time = Utilities.formatDate(
    new Date(),
    Session.getScriptTimeZone(),
    "HH:mm:ss"
  );
  SpreadsheetApp.getActiveSheet()
    .getRange("A1")
    .setValue(time);
}

function run() {
  var str =
    '<input type="button" value="start" onclick="start()"><input type="button" id="stop" value="stop" onclick="stop=true"><script>var stop=false; function work(){return new Promise((resolve, reject)=> google.script.run.withSuccessHandler(()=> resolve()).runCLock());}async function start(){while(!stop) await work();}</script>';
  var html = HtmlService.createHtmlOutput(str);
  SpreadsheetApp.getUi().showSidebar(html);
}

Expanded HTML:

At above sample script, HTML is minified. Below script is the expanded HTML.

This is only for confirming HTML. So you are not required to copy and paste this.

<input type="button" value="start" onclick="start()">
<input type="button" id="stop" value="stop" onclick="stop=true">

<script>
  var stop = false;

  function work() {
    return new Promise((resolve, reject) => google.script.run.withSuccessHandler(() => resolve()).runCLock());
  }

  async function start() {
    while(!stop) await work();
  }
</script>

Note:

  • I think that when this method is used, also the real time process can be achieved at other Google Docs.

References:

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