German gas prices into Google Sheet by API

_README.md

German gas prices by API into Google sheets

preprequisites

0. A Google account

1. create an API key at https://creativecommons.tankerkoenig.de/

2. create a Google Sheet, e.g. in Google Drive and note the alphanumerical ID in the URL

3. write in row 1 header cells date, dateUtc, iso_calendarweek, station_key, station_status, price_e5, price_e10, price_diesel -- the exact spelling doesn't matter. Google Apps Script will simply append the data from the next empty row.

4. create a new project in https://script.google.com/home

5. Find gas stations in your vincinity with an API call, e.g. in your browser to https://creativecommons.tankerkoenig.de/json/list.php?lat=52.521&lng=13.438&rad=1.5&sort=dist&type=all&apikey=00000000-0000-0000-0000-000000000002 (adapt your lat lon values and the apikey) -- in the code you will need to make a comma-separated string from those

The code

See below file "Code.gs", paste it in the new Google Script project. Rename the "Unbenanntes Projekt" to something meaningful, it will become an "app" name. The same app name you will see at https://myaccount.google.com/permissions after you granted access permissions in the next step.

First run

In the Google Script editor windows, click

When asked to "Autorisierung erforderlich Für dieses Projekt ist Ihre Erlaubnis zum Dateizugriff erforderlich." click "Berechtigungen überprüfen"

Repeated runs

In the Google Script editor, use in the left menu the "watch" symbol to define a trigger, e.g.

• Choose which function to run: main
• Which runs at deployment: head
• Select event source: time driven
• Select type of time based trigger: minutes timer
• Select minute interval: every 15 min

Code.gs

/**
 * Automated download of gas station prices
 * leveraging tankerkoenig.de API
 *
 * @author: Christian Prior-Mamulyan <cprior@gmail.com>
 * @license: MIT
 *
 * depends on config file containing apikey, ids_csv, output_spreadsheetId, output_sheetname
 */

var apikey = '00000000-0000-0000-0000-000000000002';
var output_spreadsheetId = "1Za-AcxlKMMI_bkxvHtoVej7nMzz801n8X-jqdQxAjI8";
var output_sheetname = 'Sheet1';
var ids_csv = '4429a7d9-fb2d-4c29-8cfe-2ca90323f9f8,446bdcf5-9f75-47fc-9cfa-2c3d6fda1c3b,60c0eefa-d2a8-4f5c-82cc-b5244ecae955,44444444-4444-4444-4444-444444444444';

/**
 * The main function to be called by a trigger
 */
function main() {

  json = fetchJson()
  stations = parseData(json)
  appendData(stations)

}

/**
 * returns json data like
 * {data=MTS-K, ok=true, license=CC BY 4.0 -  https://creativecommons.tankerkoenig.de, prices=
 * {005056ba-7cb6-1ed2-bceb-79dff61a8d26={status=open, e5=1.709, e10=1.649, diesel=1.509}, 
 *  005056ba-7cb6-1ed2-bceb-7a4d9fd46d26={status=open, diesel=1.509, e10=1.649, e5=1.709}}}
 */
function fetchJson() {
  var url = 'https://creativecommons.tankerkoenig.de/json/prices.php'
    + '?apikey=' + apikey + '&ids=' + encodeURIComponent(ids_csv);

  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); //todo: try/catch
  Logger.log(response);
  return JSON.parse(response)
}

/**
 * iterates json input and maps to target columns in the Google Sheet
 */
function parseData(json){

  var date = new Date();
  var dateUtc = Utilities.formatDate(date, 
                                    'Etc/GMT',
                                    'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\'');

  var i=0;
  var output = [];
  //the response ist not a list of objects but "object of objects"
  for (let key in json['prices']) {
    output.push([ date,
                  dateUtc,
                  getWeek(date),
                  key,
                  json['prices'][key]['status'],
                  json['prices'][key]['e5'],
                  json['prices'][key]['e10'],
                  json['prices'][key]['diesel']]);
    i++;
  }

  return output;

}

/**
 * depends on config file containing apikey, ids_csv, output_spreadsheetId, output_sheetname
 */
function appendData(data){

  var sheet = SpreadsheetApp.openById(output_spreadsheetId).getSheetByName(output_sheetname);

  sheet.getRange(sheet.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

}

/*
 * @see: https://stackoverflow.com/a/67570524
 */
function getWeek(date) {
  return Number(Utilities.formatDate(new Date(date), "Europe/Berlin", "u")) === 7 ? 
    Number(Utilities.formatDate(new Date(date), "Europe/Berlin", "w")) - 1 : Number(Utilities.formatDate(new Date(date), "Europe/Berlin", "w"));
}

Hi,

if the code doesn't work for you just drop a note here in the comments!

BR
//cpm