This script will create a sheet like this: (Clik on it so see a large version)
This is how you would run it:
It should be executed just past midnigth, becaus tomorrow is just become today, and tomorrows prices is empty at midnigth.
Next run should be aprox 14.00, (as the Tibber API sometimes seems to be late on getting the prices), because tomorrow prices will then have arrievd and are ready to be stored in the spreadsheet. The time of 13.10 below is just an example.
/*
This script will create a sheet with sheetName into your Google Spreadsheet
configured from the Easy Logger App
You must have this App configured and Running
Script developed by Bjørn-Willy Arntzen bwa@bwa.no
versionlog :
- 04.01.2023.1 - Fix missing clear of tomorrowsdata column
- 11.01.2023.1 - Formatting of date from tibber
*/
const startRow = 5; // the first row for real data in the sheet, should be at least 5
const sheetName = "TibberPriceInfo";
// this is the demo token, replace this with your own Tibber Api Key
const tibberApiKey = '5K4MVS-OjfWhK_4yrjOlFe1F6kJXPVf7eQYggo8ebAE';
const locale = "no-NO";
Date.prototype.addHours= function(h){
this.setHours(this.getHours()+h);
return this;
}
function toTimestampString(d) {
// typisk: 17.01.2022 kl. 18.07.41
return (d.getDate() < 10 ? '0' + d.getDate() : d.getDate() )+
"." + ((d.getMonth()+1) < 10 ? '0' + (d.getMonth()+1) : (d.getMonth()+1)) +
"." + d.getFullYear() + " kl. " +
(d.getHours() < 10 ? '0'+d.getHours() : d.getHours()) +
"." + (d.getMinutes() < 10 ? '0' + d.getMinutes() : d.getMinutes()) +
"." + (d.getSeconds() < 10 ? '0' + d.getSeconds() : d.getSeconds());
}
async function setCell(cellName, cellData) {
try {
await Homey.flow.runFlowCardAction({
uri: 'homey:app:no.bwa.easy-logger',
id: 'set-cell-delimited-data',
args: {
'cell-name': cellName,
'delimited-data': '' + cellData
},
});
console.log("setCell ", cellName, " data", cellData);
} catch(err) {
console.log(err);
}
}
async function createSheet(sheetName) {
try {
await Homey.flow.runFlowCardAction({
uri: 'homey:app:no.bwa.easy-logger',
id: 'create-sheet',
args: {
'sheet-name': sheetName
},
});
} catch(err) {
console.log(err);
}
}
await createSheet(sheetName);
try {
const query = '{ "query" \: "{ viewer { homes { currentSubscription{ priceInfo{ today { total energy tax startsAt } tomorrow { total energy tax startsAt }}}}}}" } ';
const response = await fetch(
'https://api.tibber.com/v1-beta/gql',
{
method: 'POST',
headers: {
'Authorization': 'Bearer ' + tibberApiKey,
'Content-Type': 'application/json'
},
body: query
});
if(response.status !== 200) {
throw Error("Tibber call failed. Details: ",response);
}
const body = await response.json();
// the next lines may be handy if debug loggeing is needed
//console.log(body.data.viewer);
//console.log(body.data.viewer.homes[0]);
let cellHeating = sheetName + "!"+ "A" + (startRow -3);
await setCell(cellHeating, "Todays prices");
cellHeating = sheetName + "!"+ "A" + (startRow -2);
await setCell(cellHeating, "Time for price|Energy|Total cost");
for (let i = 0; i < body.data.viewer.homes[0].currentSubscription.priceInfo.today.length; i++) {
let cell = sheetName + "!"+ "A" + (startRow + i);
let d = new Date(body.data.viewer.homes[0].currentSubscription.priceInfo.today[i].startsAt).addHours(1);
await setCell(cell, toTimestampString(d) + "|#" + body.data.viewer.homes[0].currentSubscription.priceInfo.today[i].energy +"|#" + body.data.viewer.homes[0].currentSubscription.priceInfo.today[i].total);
}
cellHeating = sheetName + "!"+ "A" + (startRow -3 + 28);
await setCell(cellHeating, "Tomorrows prices");
cellHeating = sheetName + "!"+ "A" + (startRow -2 + 28);
await setCell(cellHeating, "Time for price|Energy|Total cost");
if ( body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow.length == 0 ) {
for (let i = 0; i < 24; i++) {
let cell = sheetName + "!"+ "A" + (startRow + i + 28);
await setCell(cell, "- - - data is not ready until aprox 13.00 - - -|#0|#0");
}
}
for (let i = 0; i < body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow.length; i++) {
let cell = sheetName + "!"+ "A" + (startRow + i + 28);
let d = new Date(body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow[i].startsAt).addHours(1);
await setCell(cell, toTimestampString(d) + "|#" + body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow[i].energy + "|#" + body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow[i].total);
}
} catch (err) {
console.log("Something bad happened");
console.log(err)
}