How to Poll a Google Doc from an Add-On

How to poll a Google Doc from an add-on

The polling is done from the html code in your add-on's User Interface, calling across to server-side Apps Script functions using google.script.run.

Using jQuery simplifies this, and we can even start with the answers from jQuery, simple polling example.

function doPoll(){
    $.post('ajax/test.html', function(data) {
        alert(data);  // process results here
        setTimeout(doPoll,5000);
    });
}

The basic idea can work for Google Apps Script, if we replace the ajax calls with the GAS equivalents.

Here's the skeleton of the poll function that you would use in your html file:

  /**
   * On document load, assign click handlers to button(s), add
   * elements that should start hidden (avoids "flashing"), and
   * start polling for document updates.
   */
  $(function() {
    // assign click handler(s)

    // Add elements that should start hidden

    // Start polling for updates
    poll();
  });

  /**
   * Poll a server-side function 'serverFunction' at the given interval 
   * and update DOM elements with results. 
   *
   * @param {Number} interval   (optional) Time in ms between polls.
   *                            Default is 2s (2000ms)
   */
  function poll(interval){
    interval = interval || 2000;
    setTimeout(function(){
      google.script.run
       .withSuccessHandler(
         function(results) {
           $('#some-element').updateWith(results);
           //Setup the next poll recursively
           poll(interval);
         })
       .withFailureHandler(
         function(msg, element) {
           showError(msg, $('#button-bar'));
           element.disabled = false;
         })
       .serverFunction();
    }, interval);
  };

Add-on Example, Document Poller

This is a demonstration of the jQuery polling technique calling server-side Google Apps Script functions to detect user behavior in a Google Document. It does nothing useful, but it showcases a few things that would typically require knowledge of the user's activity and state of the document, for instance context-sensitve control over a button.

The same principle could apply to a spreadsheet, or a stand-alone GAS Web Application.

Like the UI App example in this question, this technique could be used to get around execution time limits, for operations with a User Interface at least.

Screenshot

The code builds upon the example add-on from Google's 5-minute quickstart. Follow the instructions from that guide, using the code below instead of that in the quickstart.

Code.gs

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
  DocumentApp.getUi().createAddonMenu()
      .addItem('Start', 'showSidebar')
      .addToUi();
}

/**
 * Runs when the add-on is installed.
 *
 * @param {object} e The event parameter for a simple onInstall trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode. (In practice, onInstall triggers always
 *     run in AuthMode.FULL, but onOpen triggers may be AuthMode.LIMITED or
 *     AuthMode.NONE.)
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */
function showSidebar() {
  var ui = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setTitle('Document Poller');
  DocumentApp.getUi().showSidebar(ui);
}

/**
 * Check if there is a current text selection.
 *
 * @return {boolean}  'true' if any document text is selected
 */
function checkSelection() {
  return {isSelection : !!(DocumentApp.getActiveDocument().getSelection()),
          cursorWord : getCursorWord()};
}

/**
 * Gets the text the user has selected. If there is no selection,
 * this function displays an error message.
 *
 * @return {Array.<string>} The selected text.
 */
function getSelectedText() {
  var selection = DocumentApp.getActiveDocument().getSelection();
  if (selection) {
    var text = [];
    var elements = selection.getSelectedElements();
    for (var i = 0; i < elements.length; i++) {
      if (elements[i].isPartial()) {
        var element = elements[i].getElement().asText();
        var startIndex = elements[i].getStartOffset();
        var endIndex = elements[i].getEndOffsetInclusive();

        text.push(element.getText().substring(startIndex, endIndex + 1));
      } else {
        var element = elements[i].getElement();
        // Only translate elements that can be edited as text; skip images and
        // other non-text elements.
        if (element.editAsText) {
          var elementText = element.asText().getText();
          // This check is necessary to exclude images, which return a blank
          // text element.
          if (elementText != '') {
            text.push(elementText);
          }
        }
      }
    }
    if (text.length == 0) {
      throw 'Please select some text.';
    }
    return text;
  } else {
    throw 'Please select some text.';
  }
}

/**
 * Returns the word at the current cursor location in the document.
 *
 * @return {string}   The word at cursor location.
 */
function getCursorWord() {
  var cursor = DocumentApp.getActiveDocument().getCursor();
  var word = "<selection>";
  if (cursor) {
    var offset = cursor.getSurroundingTextOffset();
    var text = cursor.getSurroundingText().getText();
    word = getWordAt(text,offset);
    if (word == "") word = "<whitespace>";
  }
  return word;
}

/**
 * Returns the word at the index 'pos' in 'str'.
 * From https://stackoverflow.com/questions/5173316/finding-the-word-at-a-position-in-javascript/5174867#5174867
 */
function getWordAt(str, pos) {

  // Perform type conversions.
  str = String(str);
  pos = Number(pos) >>> 0;

  // Search for the word's beginning and end.
  var left = str.slice(0, pos + 1).search(/\S+$/),
      right = str.slice(pos).search(/\s/);

  // The last word in the string is a special case.
  if (right < 0) {
    return str.slice(left);
  }

  // Return the word, using the located bounds to extract it from the string.
  return str.slice(left, right + pos);
}

Sidebar.html

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<!-- The CSS package above applies Google styling to buttons and other elements. -->

<div class="sidebar branding-below">
  <form>
    <div class="block" id="button-bar">
      <button class="blue" id="get-selection" disabled="disable">Get selection</button>
    </div>
  </form>
</div>

<div class="sidebar bottom">
  <img alt="Add-on logo" class="logo" height="27"
      id="logo"
      src="https://www.gravatar.com/avatar/adad1d8ad010a76a83574b1fff4caa46?s=128&d=identicon&r=PG">
  <span class="gray branding-text">by Mogsdad, D.Bingham</span>
</div>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script>
  /**
   * On document load, assign click handlers to button(s), add
   * elements that should start hidden (avoids "flashing"), and
   * start polling for document selections.
   */
  $(function() {
    // assign click handler(s)
    $('#get-selection').click(getSelection);

    // Add elements that should start hidden
    var newdiv1 = $( "<div class='block' id='cursor-word'/>" ).hide(),
        newdiv2 = $( "<div class='block' id='selected-text'/>" ).hide();
    $('#button-bar').after( newdiv1, newdiv2 );
    $('#cursor-word').html('<H2>Word at cursor:</H2><p id="cursor-word-content"></p>');
    $('#selected-text').html('<H2>Selected text:</H2><p id="selected-text-content"></p>');

    // Start polling for updates        
    poll();
  });

  /**
   * Poll the server-side 'checkSelection' function at the given
   * interval for document selection, and enable or disable the
   * '#get-selection' button.
   *
   * @param {Number} interval   (optional) Time in ms between polls.
   *                            Default is 2s (2000ms)
   */
  function poll(interval){
    interval = interval || 2000;
    setTimeout(function(){
      google.script.run
       .withSuccessHandler(
         function(cursor) {
           if (cursor.isSelection) {
             // Text has been selected: enable button, hide cursor word.
             $('#get-selection').attr('disabled', false);
             $('#cursor-word').hide();
             // $('#selected-text').show();  // Not so fast - wait until button is clicked.
           }
           else {
             $('#get-selection').attr('disabled', true);
             $('#cursor-word').show();
             $('#selected-text').hide();
           }
           $('#cursor-word-content').text(cursor.cursorWord);
           //Setup the next poll recursively
           poll(interval);
         })
       .withFailureHandler(
         function(msg, element) {
           showError(msg, $('#button-bar'));
           element.disabled = false;
         })
       .checkSelection();
    }, interval);
  };

  /**
   * Runs a server-side function to retrieve the currently
   * selected text.
   */
  function getSelection() {
    this.disabled = true;
    $('#error').remove();
    google.script.run
      .withSuccessHandler(
        function(selectedText, element) {
          // Show selected text
          $('#selected-text-content').text(selectedText);
          $('#selected-text').show();
          element.disabled = false;
        })
      .withFailureHandler(
        function(msg, element) {
          showError(msg, $('#button-bar'));
          element.disabled = false;
        })
      .withUserObject(this)
      .getSelectedText();
  }

  /**
   * Inserts a div that contains an error message after a given element.
   *
   * @param msg The error message to display.
   * @param element The element after which to display the error.
   */
  function showError(msg, element) {
    var div = $('<div id="error" class="error">' + msg + '</div>');
    $(element).after(div);
  }
</script>

Polling Interval

The setTimeout() function accepts a time interval expressed in milliseconds, but I found through experimentation that a two-second response was the best that could be expected. Therefore, the skeleton poll() has a 2000ms interval as its default. If your situation can tolerate a longer delay between poll cycles, then provide a larger value with the onLoad call to poll(), e.g. poll(10000) for a 10-second poll cycle.

Sheets

For a sheet example see How do I make a Sidebar display values from cells?

Best Method for polling a google sheet for changes using a Google Apps Script Web App?

The difference I see between using the modified date on the file versus the latest revision, is that the file will also report changes to bound scripts. Revisions will be limited to changes on the spreadsheet and exclude script changes. This is can be seen in my test below where there's several minutes difference between the two.

If you want to specifically limit the scope to spreadsheet-only changes, then the most accurate value will be reported by using revisions.

function test() {
  const fileId = FILE_ID;
  console.log(getLastRevision(fileId)); // 2021-01-12T15:32:43.175Z
  console.log(getLastUpdated(fileId)); // Tue Jan 12 2021 15:53:53 GMT+0000 (Greenwich Mean Time)
  console.log(getLastUpdatedAdvanced(fileId)); // 2021-01-12T15:53:53.941Z
}

function getLastRevision(fileId) {
  let response;
  do {
    response = Drive.Revisions.list(fileId, { maxResults: 1000 });
  } while (response.nextPageToken);
  return response.items[response.items.length - 1].modifiedDate;
}

function getLastUpdated(fileId) {
  return DriveApp.getFileById(fileId).getLastUpdated();
}

function getLastUpdatedAdvanced(fileId) {
  return Drive.Files.get(fileId).modifiedDate;
}

I don't know how many revisions you may expect a spreadsheet to have, but using a nextPageToken is quite straight-forward. However, if the spreadsheet will have many thousands of revisions and you're going to be polling this frequently, then it may not be the most effective method.

I wasn't able to replicate a delay with DriveApp.getFileById(fileId).getLastUpdated(), but it doesn't strike me as unusual. You could instead try using the Advanced Drive service, as you're doing with Revisions, to get the modifiedDate. I would not expect this to return a delayed value. As you indicated, it may be your best option as you don't need to worry about the number of revisions, but keep in mind that it also reflects non-spreadsheet updates.

Replacing words in Google Docs with Google AppScript

Real-time text replacement is problematic because it requires a way to automatically detect changes to the document. While spreadsheets support the onEdit trigger, docs do not. You can simulate this behavior, as demonstrated in this answer.

If real-time replacement is not a requirement, you can simply write a function to do a regex replacement, and add the function to the menu:

function replaceText(){
  var docBody = DocumentApp.getActiveDocument().getBody();
  docBody.replaceText('[Aa]pple','pear'); 
  //Will replace "apple", "Apple," "applesauce", "pineapple"
}

function onOpen(e){
  DocumentApp.getUi()
  .createMenu("Text Replacer")
  .addItem("Replace all Text", 'replaceText')
  .addToUi();
}

For help writing your regex, see this document.

Google form with Google sheet

Publish a webapp bound to Google sheets
Embed the Google form in your webapp
Poll your sheet from web app every minute or so.
Use LockService to avoid simultaneous execution conflicts.

Sample Image

Using google spreadsheet values for a drop down in a Google Docs Side bar

Use the Google Apps Script Spreadsheet service, first to open the spreadsheet then to get the range and lastly to get the values.

Example:

/**
 * Get the values from a range speficied by the Spreadsheet ID and the range reference or 
 * name
 *
 * @param {'1FkUd199CS3U25bfb5WxP-Jy--qcgD4NTHYWNMw8AtiA'} id Spreadsheet id
 * @param {'Sheet1!A1:A100'} stringReference Reference string
 * @return 2D array with the values of the specified range
 */
function getSpreadsheetValues(id,stringReference){
  var ss = SpreadsheetApp.openById(id);
  var range = ss.getRange(stringReference);
  return range.getValues();
}

How to capture change tab event in Google Spreadsheet?

Use the poller technique demonstrated in How to poll a Google Doc from an add-on. Unlike a time-based trigger, this relies on a client-side delay loop in the sidebar html to make calls to your script on Google's servers.

Here's how you can modify the example from that question.

in Code.gs

Add a function to get the current tab name
Replace DocumentApp with SpreadsheetApp
Optionally, delete all the irrelevant cursor / selection stuff.

/**
 * Gets the name of the current tab
 *
 * @return {string} The selected tab name.
 */
function getActiveTab() {
  return SpreadsheetApp.getActiveSheet().getName();
}

in HTML

replace the poll() function with this one.
Optionally, delete all the irrelevant cursor / selection stuff.

<div id="tab-name">loading...</div>

...

/**
 * Poll server-side function(s) at the given interval.
 *
 * @param {Number} interval   (optional) Time in ms between polls.
 *                            Default is 2s (2000ms)
 */
function poll(interval){
  interval = interval || 2000;
  setTimeout(function(){
    google.script.run
     .withSuccessHandler(
       function(tabName) {
         // DEMO - just display name of selected tab
         $('#tab-name').text(tabName);
         //Setup the next poll recursively
         poll(interval);
       })
     .withFailureHandler(
       function(msg, element) {
         showError(msg, $('#button-bar'));
         element.disabled = false;
       })
     .getActiveTab();
  }, interval);
};

How do I make a Sidebar display values from cells?

Something like this?

screenshot

This add-on uses the poller idea from How to poll a Google Doc from an add-on to call a server function getRecord(). That function grabs the row of data that is currently selected, and returns it to the showRecord() callback on the client (JavaScript) side, which handles the presentation in the sidebar.

It's not complete - watch for TODO comments. I wonder if this would be worth further developing and publishing?

Code.gs

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */

var SIDEBAR_TITLE = 'Record Viewer';

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('View records', 'showSidebar')
      .addToUi();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a sidebar. The sidebar structure is described in the Sidebar.html
 * project file.
 */
function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle(SIDEBAR_TITLE);
  SpreadsheetApp.getUi().showSidebar(ui);
}

/**
 * Returns the active row.
 *
 * @return {Object[]} The headers & values of all cells in row.
 */
function getRecord() {
  // Retrieve and return the information requested by the sidebar.
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var rowNum = sheet.getActiveCell().getRow();
  if (rowNum > data.length) return [];
  var record = [];
  for (var col=0;col<headers.length;col++) {
    var cellval = data[rowNum-1][col];
    // Dates must be passed as strings - use a fixed format for now
    if (typeof cellval == "object") {
      cellval = Utilities.formatDate(cellval, Session.getScriptTimeZone() , "M/d/yyyy");
    }
    // TODO: Format all cell values using SheetConverter library
    record.push({ heading: headers[col],cellval:cellval });
  }
  return record;
}

Sidebar.html

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<!-- Below is the HTML code that defines the sidebar element structure. -->
<div class="sidebar branding-below">
  <p>
  This sidebar displays all cells in a row, as a "record".
  </p>
  <!-- The div-table class is used to make a group of divs behave like a table. -->
  <div class="block div-table" id="sidebar-record-block">
  </div>
  <div class="block" id="sidebar-button-bar">
  </div>
  <div id="sidebar-status"></div>
</div>

<!-- Enter sidebar bottom-branding below. -->
<div class="sidebar bottom">
  <img alt="Add-on logo" class="logo" width="25"
      src="https://googledrive.com/host/0B0G1UdyJGrY6XzdjQWF4a1JYY1k/apps-script_2x.png">
  <span class="gray branding-text">Record Viewer by Mogsdad</span>
</div>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('SidebarJavaScript').getContent(); ?>

SidebarJavascript.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script>
  /**
   * Run initializations on sidebar load.
   */
  $(function() {
    // Assign handler functions to sidebar elements here, if needed.

    // Call the server here to retrieve any information needed to build
    // the dialog, if necessary.

    // Start polling for updates        
    poll();
  });

  /**
   * Poll a server-side function at the given interval, to have
   * results passed to a successHandler callback.
   *
   * https://stackoverflow.com/a/24773178/1677912
   *
   * @param {Number} interval   (optional) Time in ms between polls.
   *                            Default is 2s (2000ms)
   */
  function poll(interval) {
    interval = interval || 1000;
    setTimeout(function() {
      google.script.run
        .withSuccessHandler(showRecord)
        .withFailureHandler(
          function(msg, element) {
            showStatus(msg, $('#button-bar'));
            element.disabled = false;
          })
        .getRecord();
    }, interval);
  };

  /**
   * Callback function to display a "record", or row of the spreadsheet.
   *
   * @param {object[]}  Array of field headings & cell values
   */
  function showRecord(record) {
    if (record.length) {
      for (var i = 0; i < record.length; i++) {
        // build field name on the fly, formatted field-1234
        var str = '' + i;
        var fieldId = 'field-' + ('0000' + str).substring(str.length)

        // If this field # doesn't already exist on the page, create it
        if (!$('#'+fieldId).length) {
          var newField = $($.parseHTML('<div id="'+fieldId+'"></div>'));
          $('#sidebar-record-block').append(newField);
        }

        // Replace content of the field div with new record
        $('#'+fieldId).replaceWith('<div id="'+fieldId+'" class="div-table-row"></div>');
        $('#'+fieldId).append($('<div class="div-table-th">' + record[i].heading + '</div>'))
                      .append('<div class="div-table-td">' + record[i].cellval + '</div>');
      }
    }

    // TODO: hide any existing fields that are beyond the current record length

    //Setup the next poll
    poll();
  }

  /**
   * Displays the given status message in the sidebar.
   *
   * @param {String} msg The status message to display.
   * @param {String} classId The message type (class id) that the message
   *   should be displayed as.
   */
  function showStatus(msg, classId) {
    $('#sidebar-status').removeClass().html(msg);
    if (classId) {
      $('#sidebar-status').addClass(classId);
    }
  }

</script>

Stylesheet.html

<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">

<style>
label {
  font-weight: bold;
}

.branding-below {
  bottom: 54px;
  top: 0;
}

.branding-text {
  left: 7px;
  position: relative;
  top: 3px;
}

.logo {
  vertical-align: middle;
}

.width-100 {
  width: 100%;
  box-sizing: border-box;
  -webkit-box-sizing : border-box;‌
  -moz-box-sizing : border-box;
}

#sidebar-value-block,
#dialog-elements {
  background-color: #eee;
  border-color: #eee;
  border-width: 5px;
  border-style: solid;
}

#sidebar-button-bar,
#dialog-button-bar {
  margin-bottom: 10px;
}

.div-table{
  display:table;
  width:auto;
/*  background-color:#eee;
  border:1px solid  #666666;*/
  border-spacing:5px;
}
.div-table-row{
  display:table-row;
  width:auto;
  clear:both;
}
.div-table-td, .div-table-th {
  display:table-cell;         
  width:200px;         
  background-color:rgb(230, 230, 230);  
}
.div-table-th {
  /*float:left;*/
  font-weight: bold;
}
.div-table-td {
  /*float:right;*/
}
</style>

How to Poll a Google Doc from an Add-On