0

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?

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
sam
  • 9,486
  • 36
  • 109
  • 160

1 Answers1

1

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.

Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • friend good morning, your solution I tried to use your solution and not the sidebar, only to run from the menu or running the function, but in the onEdit event does not work – Stanley Illidge Jan 26 '18 at 13:45
  • Google may have changed the scope of actions possible in an `onEdit()` since this answer was written. If you can't figure it out on your own, you should post a new question - refer back to this one for context, to help potential answerers. – Mogsdad Jan 26 '18 at 13:53