Google Data Studio

If you want to view our data in another way, you can use our connection to Google Data Studio (GDS). GDS is a free platform created by Google to show data from al kinds of sources. It comes pre-loaded with a lot of different connectors to connect to your favourite data source. Some of these connectors include: PostgreSQL, MySQL, BigQuery, Google Spreadsheets, File Uploads...

Our Connection to Google Data Studio

For our project we utilize a PostgreSQL server, but when we tried to connect with it we ran into some issues. These issues are adressed later on in this documentation. After some researching we decided to use a connection through Google Spreadsheets. Our data stored in the database is first read by a script and then placed inside a Google Spreadsheet. In GDS you can easily select a Spreadsheet by clicking on "Add Data" and then click on the Google Spreadsheets connector. After this you can select any of your own Spreadsheets or those that have been shared with you. Finally you need to select the sheet that you want to import. After following these steps you will see your data in GDS, now you can create charts with this data.

Fetching data from Heroku

As we already mentioned, our data is stored on a PostgreSQL server in Heroku. To get data from this database we utilize "Dataclips", these are standard SQL queries that we can use to fetch data. A SQL query can look like this:

SELECT (the item you want) FROM (the database table where the data is located)

After making a query Heroku will provide the requested data in several forms, including a link to a CSV file that can be imported into GDS. This link can be found is you click on "Share" within the Dataclip.

What if you need to reset the PostgreSQL database?

Sometimes it happens that you need to reset the PostgreSQL database. No worries, the dataclips you've made aren't lost forever. Go back to the page where you previously created the dataclips. If they don't show up. Try to make a new dataclip and just return to the previous page. The old dataclips will show up again.

page with dataclips

The old dataclips don't have a datastore yet. To connect to your database, open the dropdown and select your database. That's it , it will work again!

how to choose the right datastore

Showing the data in Google Spreadsheets

If we would place the link obtained from Heroku within a cell in Google Spreadsheets, we would see the data that is currently available within the database. The downside to this is that if our database is updated, the data within Google Spreadsheets is not. Fortunately we can use a script to refresh this data for us.

The script for updating our Google Spreadsheets

For this script to work, we need several things first:

  • A Google Spreadsheet
  • A hosted CSV file (the one we created in Heroku)
  • A Google Account (if you want to host the script on Google)

We created our script in Google App Scripts, but you could host it anywhere you want. The useful thing about Google App Scripts is that it comes with a connector to interface with Google Spreadsheets.

We start by creating a new project in Google App Scripts. Before our MyFunction we need to create some variables:

const csvUrl = '(the link to the CSV we got from heroku)';
const delimiter = ',';
const googleSheetId = '(the ID from the Google Spreadsheet you want to upload to)';
const targetSheet = '(the name of the sheet where te data will be displayed)';

Inside the MyFunction we can start building our script. First we are going to fetch the CSV file:

let response = UrlFetchApp.fetch(csvUrl);
let data = response.getContentText();

Next we want to parse the data so it can be correctly imported into the Google Spreadsheets:

let pattern = new RegExp(delimiter + '(?=(?:(?:[^"]*"){2})*[^"]*$)');
let rows = data.split(/\r?\n/);
rows = rows.map((row) =>
  row.split(pattern).map((cell) => cell.replace(/^"(.*)"$/, '$1'))
);
if (!rows || !rows.length || rows.length < 1 || !Array.isArray(rows)) {
  throw new Error('Something went wrong!');
}

Now we need to add our Google Sheet service to our project. Inside your project click on the services tab and add the Google Sheets API. After we have added this service, we can update our Google Spreadsheet. Outside of our MyFunction we are going to create a new function:

function columnToLetter(columnNum) {
  let temp;
  let letter = '';
  while (columnNum > 0) {
    temp = (columnNum - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    columnNum = (columnNum - temp - 1) / 26;
  }
  return letter;
}

The function above is used to give each column a letter, this is because Google Spreadsheets doesn't use numbers to indicate it's colummns. After we have done this we can continue to update our Google Spreadsheet, back inside the MyFunction add the following code:

let endingColumn = columnToLetter(rows[0].length);
let range = `${targetSheet}!A1:${endingColumn}${rows.length}`;
let valueRange = Sheets.newValueRange();
valueRange.values = rows;
Sheets.Spreadsheets.Values.clear({}, googleSheetId, `${targetSheet}!A:ZZ`);
Sheets.Spreadsheets.Values.update(valueRange, googleSheetId, range, {
  valueInputOption: 'USER_ENTERED',
});

Now we can test our script by clicking "Run", it might happen that you are asked to grant certain permissions to the script, to continue you need to accept these permissions. After the script has run, you should see all your data in Google Spreadsheets.

If we want our script to run periodically, we need to add a trigger in Google App Scripts. You can click on Add Trigger and then enter how often you want your script to run. For this project we decided to let the script update our Google Spreadsheets every minute.

If you want to copy paste the script, you can find it here:

const csvUrl = 'https://data.heroku.com/dataclips/hfmxojuytxqsnzuxqmvxxcafvwth.csv'; // The URL where your CSV file is located.
const delimiter = ','; // Default is comma, can change if necessary.
const googleSheetId = '1nXDleMdszLd8-vZUg-O46ideT-RIHeSHdeuT_y5neM8'; // This ID can be found in the URL of your Google Spreadsheets.
const targetSheet = 'Cars'; // Name of the tab you want to import the CSV into.

function myFunction() {
    let response = UrlFetchApp.fetch(csvUrl);
    let data = response.getContentText();
  let rows = data.split(/\r?\n/);
    let pattern = new RegExp(delimiter + '(?=(?:(?:[^"]*"){2})*[^"]*$)');
    rows = rows.map((row) =>
        row.split(pattern).map((cell) => cell.replace(/^"(.*)"$/, '$1'))
    );

    if (!rows || !rows.length || rows.length < 1 || !Array.isArray(rows)) {
        throw new Error('Something went wrong!');
    }


    let endingColumn = columnToLetter(rows[0].length);
    let range = `${targetSheet}!A1:${endingColumn}${rows.length}`;
    let valueRange = Sheets.newValueRange();
    valueRange.values = rows;
    Sheets.Spreadsheets.Values.clear({}, googleSheetId, `${targetSheet}!A:ZZ`);
    Sheets.Spreadsheets.Values.update(valueRange, googleSheetId, range, {
        valueInputOption: 'USER_ENTERED',
    });
}

function columnToLetter(columnNum) {
    let temp,
        letter = '';
    while (columnNum > 0) {
        temp = (columnNum - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        columnNum = (columnNum - temp - 1) / 26;
    }
    return letter;
}