How to set up automatic updates on a Google Sheet

This is a tutorial on how to upload and import a live-updating CSV File into a Google Spreadsheet using Google Apps Script. 

Importing static CSV data into a Google spreadsheet can be as easy as doing File > Import > Upload. However, when you have a link to a dynamic CSV file that is being regularly updated, and want to import it into your own Google Spreadsheet to edit the data, it's not as simple. 

1

Create a new document, and go to  Extensions > Apps Script 

2

Go to  Files > + Script. Give it whatever name you like (e.g. Code.gs) (①)

Paste in the following script as shown above (②). Make sure to replace the link https://your-data-source.csvwith your data link. Also note that if you give your sheet a different name, you'll have to swap Sheet1 for your chosen name.

function myFunction() {
  function importData()
  {
    var ss = SpreadsheetApp.getActive();
    var url = 'https://your-data-source.csv';
    var text = UrlFetchApp.fetch(url).getContentText();
    var csvData = Utilities.parseCsv(text);
    var sheet = ss.getSheetByName('Sheet1');
    for (var i = 0; i < csvData.length; i++) {
      sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    }
  }
  importData();
}
	

If you don't want to set this up yourself, you can simply copy this Google sheet we've prepared for you and swap out the link for your CSV link. To copy, you'd have to be logged in to your google account. 

3

File > Save to save and name the project, choose whatever name you like (e.g. automatic-import-script) 

4

Test that it works by clicking the Run ▶ button: 

A pop-up will ask you to Review permission. Click to continue:  To move forward, you'll have to click on Advanced > Go to name-of-your-project (unsafe)   and click on Allow: 

Now, you can click on the Run ▶ button again and this time your script should run properly. Check by opening the Execution log and you should see the following two lines if the execution is successful: 

5

Go back to the Google sheet - you should now find that the data is in there: 

6

Now, set up that the script runs automatically at regular time intervals. To do so, go back to the Google Apps Script Dashboard and from the sidebar menu on the left, go to Triggers > + Add Trigger

7

In the dialogue that opens choose the event source as Time-driven 

8

You can then chose how often you want it to run: 

9

That's it! Your sheet is ready, you can go back to your google sheet then turn on sharing by clicking the "Share" button on the top-right corner: 

Make sure that your google sheet is set to " Anyone with the link" can view or share the sheet with data@datawrapper.de.

Click on Copy link and paste it into Datawrapper in the Link external dataset configuration. If you're not familiar with what this is, check out our academy article "Automatic chart updates: How to publish charts with external data sources":

That's it! If you have any questions, reach out to support@datawrapper.de.