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

submit.md

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:

• Class google.script.run
• Class Ui
• Infinite-Loop GoogleAppScript
  • This sample script was also used for this thread.