How to refresh a Google Sheet using Google Apps Script

Learn how to use Google Apps Script to update the last modified timestamp in Google Sheets, essential for ensuring accurate data sync with Databox.

IN THIS ARTICLE

• Preface
• Use Case
• Setup Instructions
  • Apps Script
  • Trigger
• Operation

Preface

Google Apps Script is a JavaScript-based language used to automate tasks and extend Google Workspace apps like Google Sheets. Within Sheets, it enables users to automate data entry, create custom functions, build add-ons, and integrate with other Google services. For instance, it can automate tasks like fetching external data and triggering emails based on specific conditions, streamlining workflows and boosting productivity within spreadsheets.

This article will demonstrate how to utilize Apps Script to update the timestamp of the last modification in the Google Sheet. This timestamp is crucial for Databox as it allows us to determine if the document has been updated since the last check. In such cases, we download the document and parse its contents during the next scheduled sync, ensuring the new data is reflected on your reports.

Use Case

When you update your Google Sheets data using tools such as Google Forms, functions, scripts, or third-party integrations, you may find that these changes don't appear in your Databox reports for hours or even days. This happens because the last modified timestamp, which our system relies on to fetch new data, doesn't always update correctly. Consequently, the updated or new date may not be recognized or included in your reports as anticipated.

By implementing this solution, your document's last modified timestamp will be updated regularly, ensuring that our system downloads and parses the document's contents as intended.

Setup Instructions

Apps Script

1. Log in to your Google account and navigate to Google Sheets.
2. Once your spreadsheet is open, go to the menu bar and select Extensions > Apps Script. This action will open the Script Editor in a new tab.
3. In the Script Editor, replace the contents of the "Code.gs" script with the code below.
   

   function refresh() {
   
     // Get current datetime.
     timezone = "GMT+" + new Date().getTimezoneOffset()/60
     var now = Utilities.formatDate(new Date(), timezone, "yyyy-MM-dd'T'HH:mm:ss'Z'");
     
     // Opens the active spreadsheet file.
     const ss = SpreadsheetApp.getActiveSpreadsheet();
   
     // Gets the developer metadata object.
     const developerMetaData = ss.getDeveloperMetadata();
   
     // Check if our metadata key has already been created.
     firstRun = true
     modifiedTime = false
     for (var i = 0; i < developerMetaData.length; i++) {
       if (developerMetaData[i].getKey() == 'modifiedTime') {
         firstRun = false
         modifiedTime = developerMetaData[i]
       }
     }
   
     if (firstRun) {
       // Adds the key 'modifiedTime' and sets the value to the current datetime in the developer metadata for the spreadsheet.
       ss.addDeveloperMetadata('modifiedTime', now);
       // console.log(`Key: modifiedTime, Value: ${now}`);
     }
     else {
       // Updates the value of the metadata key.
       modifiedTime.setValue(now);
       // console.log(`Key: ${modifiedTime.getKey()}, Value: ${modifiedTime.getValue()}`);
     }
   
   }

    

4. After updating the script, click on the floppy disk icon to save it, or use shortcuts like CTRL+S/ ⌘+S. You can give your project a name too and save it within the Script Editor.

Trigger

1. In the Script Editor, click on the clock icon labeled Triggers in the toolbar. This opens the triggers panel.
2. Inside the triggers panel, click on the + Add Trigger button at the bottom right-hand corner.
3. Configure the trigger:
   1. Leave the function to run as selected (i.e. refresh).
   2. Select Time-driven for the type of event source.
   3. Choose Minutes timer as the type of time-based trigger.
   4. Set Every minute in the next dropdown.
4. After setting up the trigger configurations, click Save to create the time-driven trigger.
5. The trigger requires authorization to execute the function on behalf of your Google account. Follow the prompts that appear in the authorization dialog boxes to grant the necessary permissions.
6. Once saved, the trigger will now be visible in triggers panel.

Now, the refresh() function will execute automatically every minute as per the time-driven trigger you've set up in your Google Apps Script associated with the Google Sheet. Adjust the function name and trigger settings as needed for your specific use case.

Operation

The script works by updating the developer metadata of the Google Sheets document.

Developer metadata in Google Sheets refer to pieces of information that developers or scripts can attach to cells, sheets, or other parts of a spreadsheet. These metadata elements provide additional contextual information or settings for specific elements within the sheet. 

Google treats changes in this metadata as edits to the document, prompting an update to the last modified timestamp. However, it's essential to note that Google's update frequency for this timestamp is limited, typically refreshing at intervals of approximately every three minutes.