Anytime you are regularly updating data into a spreadsheet you should ask yourself if there is an easier way to do things. 

Are you pulling data from a partners dashboard?

Are you exporting from a BI tool?

Are you getting a daily email and pasting the new columns into your spreadsheet?

Automating regular data updates can save you a ton of time so it’s worth the effort exploring anyway you can make the process less reliant on manual work.

This is an extremely common issue for many companies and it usually falls on a analyst to do the grunt work each morning.

The first step in making the process better is to think about your issue. Understand the source of your data and look for all the options it gives you to export.

In this case, it is ideal if your data source has an available API that allows you to export as a CSV. If so you are in luck and can probably export it directly into Google Sheets.

We use Google Apps Script as our resource for getting things done and it’s by far our main reason for choosing Google Sheets over Excel.

See the following example that we got to work:

// name whatever you want
function getData() {
// api url
var url = 'https://websiteigetdatafrom.com/API/version/export';
// if you need a token
var apiToken = '';
// any parameters that are required for your data pull
var apiParams = {
     'project_name': 'reports',
     'token': apiToken,
     'split_by':['client_name','date'],
     'start_date': '2020-01-01',
     'end_date': '2020-12-31',
     'data_fields': ['impressions','revenue',],
}
// posts the data                     
var payload = JSON.stringify(apiParams);
var params = {
    'method': 'POST',
    'payload': payload,
    'muteHttpExceptions' : true,
  };
                     
var response = UrlFetchApp.fetch(url, params)
// makes it into a csv
var csvData = Utilities.parseCsv(response);
// select the spreadsheet name
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("api_data");
// set where in the spreadsheet you want to post it
ss.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

 

 

You will certainly need to modify the code to make it work for you. Here is the google sheets script docs that can help but you may be better off looking for your specific case on community forums. If you get stuck just post a question to stackoverflow and you’ll sometimes be surprised how much a stranger can help.

For more tips and tricks for Google Sheets, visit our Data & Analytics resource center.