How to update my map automatically when the data changes

Enable Unattended time-based BUILD

On each document you want to use this feature, just do as follows

  • Go to add-on Preferences > Document > Select the Unattended BUILD (time-based trigger) option

    • You'd need to BUILD the map first once, to get access to this option

  • The first time on each document, it will ask you to authorise the Script to access the spreadsheet

    • Select the spreadsheet name from the list and click the Select button to authorise

    • After that, you'd need to select Unattended BUILD again, to confirm the authorization

  • Then choose the time frequency in hours for the add-on to check if there was a change in the datasheet

  • Save Preferences to activate the Unattended BUILD option

  • From that point on, the add-on would check every number of hours (Frequency), if the spreadsheet has changed after the last BUILD was completed

    • If the data changed, the add-on would BUILD the map again (like the user clicking on the BUILD button)

    • If the last BUILD was not completed due to quota limits or other issues, the add-on would continue the BUILD process, unattended until completed.

  • This is a great feature when Geocoding a large number of locations that will save you time, effort and clicks on the BUILD button

Disclaimer

  • If you are using Google Sheets formulas (like IMPORTRANGE and others) looking up to another spreadsheet for updates, that won't change the last modified date in the spreadsheet file unless/until you open it, so it won't be Unattended BUILD calls

    • If that is the case, you can ask us for access to the â–ˇ Always switch under Preferences > Document, that would do the BUILD regardless

  • As the BUILD process is unattended, you won't get any notification due to errors or issues as you get when clicking on the BUILD button

    • We aim to notify the user about any error we get in the add-on Log so it can be fixed manually by the user

  • The map update is not in realtime, you would need to refresh/reload the Mapping web app to see the latest changes

  • Even though you authorized the general scope "Allow this application to run when you are not present" when using the add-on,

    • the Unattended BUILD feature will only work on those spreadsheets where you explicitly enable the option and authorise the access

  • The Unattended BUILD feature will stop working in any of the following cases

    • You disable the Unattended BUILD option under Preferences > Document

    • You revoke (remove access) the add-on permissions under your Google account

    • You lose access to the Google spreadsheet

    • If the add-on is uninstalled by the user

    • If the add-on is disabled in a document

  • The Unattended BUILD feature will be disabled to avoid excessive failed executions, if there is an unrecoverable error due to any of the following causes

    • If the name of the datasheet (sheet with your data within the spreadsheet) changed after the last manual BUILD

    • If the name of any of the headers you chose as Filter, Title, Location changed after the last manual BUILD

    • If you lose access to the spreadsheet or the Mapping folder

    • You would get a notification about this action, then you would need to BUILD the map and enable the Unattended BUILD feature again


TL;DR

Find some further details and screenshots below

You would need to BUILD a map first (even a partial one) on each datasheet, to get access to the Unattended BUILD option, otherwise it'd show up disabled (greyed out)

After your first BUILD, you would be able to access and enable the Unattended BUILD option, as well as selecting the frequency in hours (1,2,4,6,8,12)

On Google Editor add-ons time-driven triggers cannot run more frequently than once per hour.

/dev: see restrictions on add-on time-based triggers

When selecting the Unattended BUILD option (click on checkbox) for the first time, on each datasheet

  • It will open the Google Drive File Picker and

  • You will be asked to select the current/active spreadsheet name from a list

  • Just click on that name (Randomized in this case)

    • The list might have more than one spreadsheet if there is some partial match on their names

    • If you choose other spreadsheet on the list, it will keep asking you to select the current one

  • Then click on Select button to provide access to this spreadsheet

  • This access is required for the add-on to get the last modified date of the spreadsheet using the Google Drive Api and compare it with the last BUILD date

Note that after providing authorisation to the current spreadsheet

  • You'd need to select the Unattended BUILD option again to confirm it

  • Also you can select the frequency in hours

  • And finally Save the Preferences to activate the process

  • The add-on will create a time-based trigger to call the Unattended BUILD process on the frequency you set

  • The exact time for the call will be set randomized when creating the trigger

Issues or Limitations

The feature is working fine but there are some potential issues, as the user is not there when the BUILD happens

  1. If the name of the spreadsheet changed after the last manual BUILD, the add-on would update a new map, due to the change in the .json file name, the updated map link would be different

    1. Fix? Either restore the original spreadsheet name to keep the map link, or start using the new map link

  2. If the name of the datasheet (sheet with your data within the spreadsheet) changed after the last manual BUILD, the add-on will throw an error when trying to find the original datasheet by name

    1. Fix? Either restore the original datasheet name, or click BUILD to update the new name and map link, then re-enable the Unattended BUILD feature

  3. If the name of any of the headers you chose as Filter, Title, Location changed after the last manual BUILD, the add-on will not update the map

    1. Fix? Either restore the original headers names, or click BUILD to update the map settings, then re-enable the Unattended BUILD feature

  4. If the name of any of the headers you chose as additional filters changed after the last manual BUILD, those additional filters will not be included in the updated map

    1. Fix? Either restore the original headers names, or reset the additional filter headers and click BUILD to update the map settings

  5. If there is an issue when Geocoding a location address (either not found or error), that location will not be included in the map. In the extreme case that all the location in the datasheet fail on Geocoding, the map will not be updated

    1. Fix? Do a manual BUILD to get the list of those locations with Geocoding issues and fix them as required

  6. If the spreadsheet is deleted (trashed) the time-based trigger will keep running even though the file is in the Trash

    1. Fix? Restore the spreadsheet then disable the trigger

    2. If you delete the spreadsheet forever in the Trash, the time-based trigger will keep running and throwing a "File not found" error due to lack of access

    3. Otherwise, you can delete any of your Google Apps Script triggers from the Apps Script dashboard under My Triggers

  7. If the spreadsheet was shared with you and your access is revoked after you set the the unattended BUILD, the time-based trigger will keep running and throwing a "File not found" error due to lack of access

    1. Fix? This is a real bug that I hope Google fixes eventually.

    2. In the meantime you cannot do anything unless the spreadsheet is shared again with you, then you can disable the trigger.

  8. If the Unattended BUILD process gets interrupted (by an error, timeout, quota limit, etc) the map might not get updated on that call

    1. Fix? In most cases there is no action required, the process will resume in the next trigger call