Page History

/*

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

versionversionlog :
- 0304.01.2023.1 - Fix missing clear of tomorrowsdata column
- 11.01.2023.1 - Formatting of date from tibber
- 18.01.2023.1 - More effectice execution of Google API calls. 
               - settable delimiter

*/

const startRow = 5; // the first row for real data in the sheet, should be at least 5
const sheetName = "TibberPriceInfo"'TibberPriceInfo';
const delimiter = '|';  // change this to the delimiter your app has, but its mandatory to NOT BE ;

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

const locale = "no-NO";  // currently not implementet yet



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

function toTimestampString(d) {
 
        // typisk: 17.12.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: '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);
    }
}


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);
    }  
}

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;"+delimiter+"Energy"+delimiter+"Total cost"); 


  /*
    
    ={1;2;3}|={4;5;6}|={7;8;9}  

    gir dette resultatet :

    1   4   7
    2   5   8
    3   6   9

  */
  let column1 = '={';
  let column2 = '={';
  let column3 = '={';

  // column 1 
  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, d.toLocaleString(localecolumn1 += '"' +  toTimestampString(d) +  '";#"';
    column2 += body.data.viewer.homes[0].currentSubscription.priceInfo.today[i].energy +";#";
    column3 += body.data.viewer.homes[0].currentSubscription.priceInfo.today[i].total ;



    if(i < body.data.viewer.homes[0].currentSubscription.priceInfo.today.length-1) {
      column1 += ";" ;
      column2 += ";" ;
      column3 += ";" ;
    }


  }
  column1 += "}";
  column2 += "}";
  column3 += "}";


  /*  TOMOROW   */ 

  let cell = sheetName + "!"+ "A" + (startRow );
  await setCell(cell, column1 + delimiter +"#" + column2 + delimiter +"#" + column3); 


  cellHeating = sheetName + "!"+ "A" + (startRow -3 + 28);
  await setCell(cellHeating, "Tomorrows prices"); 

  cellHeating = sheetName + "!"+ "A" + (startRow -2 + 28);
  await setCell(cellHeating, "Time for price;Energy;"+delimiter+"Energy"+delimiter+"Total cost"); 


  if ( body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow.length == 0 ) {
    column1 = '={';
  column2 = '={';
  column3 = '={';

  // column 1 
  for (let i = 0; i < 24; i++) {

    // ikke data klart
    if ( body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow.length == 0 ) {

      let cell = sheetName + "!"+ "A" + (startRow + i + 28);

      await setCell(cell, column1 += '"- - - data is not ready until aprox 13.00 - - -;#0")';
    }

  }

  for (let i = 0; i < body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow.length; i++) {   column2 += '0';
      column3 += '0';



    } else {

    let cell =// sheetNamedata + "!"+ "A" + (startRow + i + 28);
    er klart

      let d = new Date(body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow[i].startsAt).addHours(1);
    await setCell(cell, d.toLocaleString(locale  column1 += '"' +  toTimestampString(d) + '";#"';
      column2 += body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow[i].energy ;
    +  ";#"column3 += body.data.viewer.homes[0].currentSubscription.priceInfo.tomorrow[i].total ;
    }

    if(i < 23) {
      column1 += ";" ;
      column2 += ";" ;
      column3 += ";" ;
    }


  }
  column1 += "}";
  column2 += "}";
  column3 += "}";

  cell = sheetName + "!"+ "A" + (startRow+28 );
  await setCell(cell, column1 + delimiter + "#" + column2 + delimiter + "#" + column3); 


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