# Refresh a Google Sheet using Google Apps Script

[Google Apps Script](https://developers.google.com/apps-script/overview) 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 the system to determine if the document has been updated since the last check. In such cases, Databox downloads the document and parses its contents during the next scheduled sync, ensuring the new data is reflected on your content.

## 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 Databox 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 Databox downloads and parses the document's contents as intended.

Please note that some functions only recalculate when the document is open in the browser. If your data relies on these functions, there is unfortunately no simple way to remotely force them to recalculate. You will need to open the file in your browser to refresh the data in the document.

## Set up the 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.

```json
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.


![Apps Script](/assets/refresh-a-google-sheet-using-google-apps-script_1.2f94f4a5212c68d4a1b0afbabd039b4c838be07ccbe0420f236cced0e9fc9e17.4783b0ec.gif)

## Set up the 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 15 minutes** 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 15 minutes 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.

![Trigger](/assets/refresh-a-google-sheet-using-google-apps-script_2.3efd100cd3ffc813b80b7c837956d7c793a5aa3d670a906f54c7f7d0c15e6eaa.4783b0ec.gif)

## Script behavior

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.

Ask Genie
Get instant answers or help with your data using the in-app AI assistant.

Talk to an expert
For customers: Get help with your setup, strategy, or making the most of Databox.

Book a demo
New to Databox? See how it works and get guidance on getting started.

Send an email
Reach out to support for help with your account, data, or technical issues.