Page History

/*

This script will create a sheet with sheetNamePrefix into your Google Spreadsheet

configured from the Easy Logger App

You must have this App configured and Running

You should correct the calulateNettLeieFor function to reflect your NettLeie

Script developed by Bjørn-Willy Arntzen bwa@bwa.no

version : 03.01.2023.1
version : 09.01.2023.1 
 - major change: delimiter is | which is a better value than ;
 - several improvements
 - language support
version : 09.01.2023.2
 - new column : Total cost wo/norwegian support w/fixed price
*/

const locale = "no-NO";
const language = "no"; // or set "en" to get english headings
const sheetNamePrefix = "TibberData-";
const startRow = 5; // use value 5 or higher

// this is the demo token, replace this with your own Tibber Api Key
const tibberApiKey = '5K4MVS-OjfWhK_4yrjOlFe1F6kJXPVf7eQYggo8ebAE';

console.log("args", args);

var countOfHours = parseInt(args)

if (isNaN(countOfHours)) {
  countOfHours = 24; // the default is 24 if argument is not set
}

console.log("countOfHours", countOfHours);

async function createSheet(sheetName) {


    try {
      await Homey.flow.runFlowCardAction({
        uri: 'homey:app:no.bwa.easy-logger',
        id: 'homey:app:no.bwa.easy-logger:create-sheet',
        args: {
          'sheet-name': sheetName
        },
      });

    } catch(err) {
      console.log(err);
    }  
}


async function setCell(cellName, cellData) {


    try {
      await Homey.flow.runFlowCardAction({
        uri: 'homey:app:no.bwa.easy-logger',
        id: 'homey:app:no.bwa.easy-logger:set-cell-delimited-data',
        args: {
          'cell-name': cellName,
          'delimited-data': '' + cellData
        },
      });

      //console.log("setCell ", cellName, " data", cellData);
    } catch(err) {
      console.log(err);
    }
}

function calulateNettLeieFor(time) {
  
  // Glitre Energi sine priser
  if (time < 22 && time > 06) {
    //console.log("calulateNettLeieFor", time, " > ", 28)
    return 0.394;
  } else {
    //console.log("calulateNettLeieFor", time, " > ", 16)
    return 0.274;
  }

}


Date.prototype.addHours= function(h){
    this.setHours(this.getHours()+h);
    return this;
}

console.log("Getting data from Tibber API");


try {
  const response = await fetch(
    'https://api.tibber.com/v1-beta/gql', 
    {
      method: 'POST', 
      headers: {
        'Authorization': 'Bearer ' + tibberApiKey,
        'Content-Type': 'application/json'
      },
      body: '{"query":"{viewer{homes{address{address1}consumption(resolution:HOURLY, last: '+countOfHours+') {nodes{from to cost unitPrice unitPriceVAT consumption consumptionUnit}}}}}"}'
  });

  //console.log("raw-response",response);
  //console.log("response status ",response.status);

  if(response.status !== 200) {
    throw Error("Tibber call failed. Details: ",response);
  }
  
  const body = await response.json();

  //console.log("homes",body.data.viewer.homes);
  //console.log("consumption",body.data.viewer.homes[0].consumption);



  var _from;
  var _consumption;
  var sheet = '';
  for (let i = 0; i < body.data.viewer.homes[0].consumption.nodes.length; i++) {

    _from = body.data.viewer.homes[0].consumption.nodes[i].from;
    _consumption = body.data.viewer.homes[0].consumption.nodes[i].consumption;

    if(_consumption === null)
      _consumption = 0;

    // må legge til en time for at klokka skal bli rett
    // from:  2023-01-01T14:00:00.000+01:00  >>  2023-01-01T14:00:00.000Z
    let d = new Date(_from).addHours(1);

  // starter på 0
    let time_offset = d.getHours();
    let dag_offset = d.getDate();
    let month_offset = d.getMonth()+1;
    if (month_offset < 10) month_offset = '0'+ month_offset;
    let year_offset = d.getFullYear();
    let dag_skew = 24 * (dag_offset - 1 );


    var _sheet =  sheetNamePrefix + month_offset + "-" + year_offset;

    if(_sheet !== sheet) {
      sheet = _sheet;
      await createSheet(sheet); // Blir opprettet om det ikke finnes, ellers feiler silent
      let cellHeating = sheet + "!"+ "A" + (startRow -2);
      if(language==="no")
        await setCell(cellHeating, "Tid for forbruk|Forbruk|Timespris (m/nettleie og avg)|Timespris (m/avg u/nettleie)|Spotpris (u/mva u/avg)|Total kost (m/forbruk u/nettleie)|Timespris m/støtte og nettleie|Total kost m/støtte og nettleie|Nettleie|Total kost u/støtte m/nettleie"); 
      else
        await setCell(cellHeating, "Time for consumption|Consumption|Price per hour (w/fixedprice and tax)|Price per hour (w/tax wo/fixedprice)|Spotprice (wo/tax)|Total cost (w/consumption wo/fixedprice)|Price per hour w/norwegian support w/fixedprice|Total cost w/norwegian support w/fixedprice|Fixedprice|Total cost wo/norwegian support w/fixedprice"); 

      cellHeating = sheet + "!"+ "A" + (startRow -4);
      if(language==="no")
        await setCell(cellHeating, "Strømstøtte|=(SUM(F6:F800) / count(F6:F800) -0,7)*0,9*1,25"); 
      else
        await setCell(cellHeating, "Norwegian support|=(SUM(F6:F800) / count(F6:F800) -0,7)*0,9*1,25"); 

      cellHeating = sheet + "!"+ "A" + (startRow -3);
      if(language==="no")
        await setCell(cellHeating, "Gjennomsnittsverdier||=SUM(D6:D800) / count(D6:D800)|=SUM(E6:E800) / count(E6:E800)|=SUM(F6:F800) / count(F6:F800)|=SUM(G6:G800) / count(H6:H800)|=SUM(H6:H800) / count(H6:H800)|=SUM(I6:I800) / count(I6:I800)||=SUM(K6:K800) / count(K6:K800)"); 
      else
        await setCell(cellHeating, "Average values|=SUM(D6:D800) / count(D6:D800)|=SUM(E6:E800) / count(E6:E800)|=SUM(F6:F800) / count(F6:F800)|=SUM(G6:G800) / count(H6:H800)|=SUM(H6:H800) / count(H6:H800)|=SUM(I6:I800) / count(I6:I800)||=SUM(K6:K800) / count(K6:K800)"); 

    }

    let num = (startRow + dag_skew + dag_offset+ time_offset);
    let cell = sheet + "!"+ "A" + num;

    let unitPrice = body.data.viewer.homes[0].consumption.nodes[i].unitPrice;
    let spotPrice = (body.data.viewer.homes[0].consumption.nodes[i].unitPrice-0.01)/1.25;

    let nettleie = calulateNettLeieFor(time_offset);

    console.log("from: ",_from, " cons " + _consumption , " d", d); 

    let totalCost = body.data.viewer.homes[0].consumption.nodes[i].cost;
    if(totalCost===null)
      totalCost = 0;

    await setCell(cell, d.toLocaleString(locale) +  
      "|#" + _consumption +   // forbruk
      "|#" + (unitPrice + nettleie) +   // Pris (m/nettleie)
      "|#" + unitPrice +  // Pris (u/nettleie)
      "|#" + spotPrice +   // Spotpris
      "|#" + totalCost + // total kost (u/nettleie)
      "|=D"+ num +"-$C$1" + // pris m/støtte og nettleie
      "|=H"+ num + "*C"+num + // Total kost m/støtte og nettleie
      "|#" + nettleie +  // Nettleie
      "|#=G" + num + "+J" + num   // Total kost u/støtte m/nettleie
      ); // # tvinger tallformat


    
  }




} catch (err)  {
  console.log("Something bad happened");
  console.log(err)
}


console.log("DONE");