CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
sam
sam

Reputation: 10094

Tracking changes in a Google Spreadsheet via a Google apps script

I'm trying to build a Google spreadsheet script to track changes in a range of cells say c10:y100, if any of those cells are changed and the user tries to leave the page, I'd like to have a confirm dialog saying "you need to update the revision number of this document".

I've looked at various ways of doing this but could not find the core function that would allow me to trigger this if a user tries to leave the page, with out manually changing the revision number, after making a change in that range.

Any ideas how I would do this?

Upvotes: 0

Views: 5256

Answers (1)

Mogsdad
Mogsdad

Reputation: 45750

Using Google Apps Script, there is no general feedback on how a user is behaving in the client-side UI - we can't tell if they close the browser tab or browser, for example. Without that, your objective can't be fully met.

You could get partway there by adapting the technique described in How to poll a Google Doc from an add-on to your spreadsheet, and applying a few other tricks...

You can get information about what spreadsheet content is "active":

  • Spreadsheet.getActiveSheet() will tell you the active "tab"
  • Sheet.getActiveCell() and Sheet.getActiveRange() can tell you where the user is on the active sheet. (Not much interest in this case, but still worth noting.)

An onEdit() function can note manual edits to the range you're interested in. It can also open the sidebar when a relevant edit is made! You could have the content of the sidebar encourage or even facilitate generation of your new revision number, for instance by providing a button to complete the task.

// onEdit function that opens sidebar if a particular cell is edited.
function onEdit(e) {
  if (e.range.getSheet().getName() === "Sheet1"
      && e.range.getA1Notation() === "A1") {
    // Edit was performed in our target range

    // First, make a note of the change.

    // Then open our tracking sidebar
    var html = HtmlService.createHtmlOutput('<H2>Up-Revision required</H2>')
                          .setTitle('Tracking sidebar')
                          .setWidth(300);

    var ui = SpreadsheetApp.getUi();
    ui.showSidebar(html);
  }

  // Ignore all other edits
}

Enforcement: There's not much you can do with any web application to stop users from getting up and walking away (or closing a window). Still, you will want to do your best to help them do the right thing.

  • As mentioned earlier, make it easy for your users, by helping them to set a new revision number.

  • If your sidebar poller notes the trigger condition (edit in C10:Y100 without up-versioning) when the user changed tabs, say, then it can open a modal dialog to enforce the up-versioning.

  • If you've noted the un-revisioned change in a non-volatile way (using Document Properties, say), then you could have an onOpen() trigger function perform the test and open the sidebar &/or the modal dialog.

Upvotes: 1

Related Questions