How can you refresh a Google Sheet export?

How it works

When you export your responses to a Google Sheet, Google will dynamically pull all of your responses into the sheet and periodically refresh the your response data when the sheet is loaded.

This guide shows you how to add a button to your Google Sheet so that you can refresh your response data on demand.

Setup Guide

Let’s walk through the steps to setup on demand data refresh.

Add an image to your Google Sheet Export

This can be any type of image. Typically when inserting an image it is helpful to insert the image “over cells”.

Once you have the image inserted your spreadsheet, next we will assign a script that will be run when the image is clicked. You can trigger a drop down menu by selecting the image and clicking on the ellipses. Select “Assign Script”.

Assign the script in the dialog called “refreshData”.

Setup an Apps Script on your sheet

Next we will create the function “refreshData” in the Apps Script of your sheet. Click “Apps Script” on the “Extensions” menu option in Google Sheets.

Inside of the Apps Script dashboard, create a file named dataImport.gs. After creating the file, add the following code. The following script will momentarily delete a cell on your spreadsheet when the function is triggered.

function refreshData() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  console.log("refreshing data for " + sheet)
  var A1value = sheet.getRange('A1').getFormula(); // get the formula of A1 
  sheet.getRange("A1").setValue("");
  SpreadsheetApp.flush();
  sheet.getRange("A1").setFormula(A1value); // set the formula of A1 to A1  }  
}

Click the “Save” icon after you have completed your changes.

Try it out!

Now that the function has been defined and is attached to you image, you can click the image to run the script. The first time you run the script you will be asked to authorize the script. Click “OK”.

Related Guides