Send data to google sheets

Projects / Brainstorming
#1

Hi

Does anyone know if it would be possible to send eg. charging data from an EV to google sheets.

I would like to analyse the charging cycle of my Hyundai Ioniq, to understand the planned charging better.

Thx.

#2

Mikael,

Not an easy answer however I can send you a sample google script that calls a rest end point and populates a google sheet . You will need change it quite a bit to add support for initial key request etc,

I am doing it a. Bit of a round trip. I extract to Mongo DB by using Mongos great stitch sererless function and then have it provide a rest end point onto google sheets

Let me know

#3

Hi,

I did the job with MS Excel. The macro can read logged data via Autopi-API.
Have fun with it and feel free to improve code.

GetDataFromWebAPI.xlsm

Manfred

#4

Great, thanks much appreciated

#5

Thx. Chris and Manfred. I would though still like to see if I can push data to google sheets, with a job and some custom code.
I would like to build up some historical data, so that I can draw a graph of eg. a charging session.

#6

Mikael,

I have a simple script which should work to get you started. Go into google sheets and

  1. create a sheet called test
  2. In tools/script editor paste the following code in (you will need to change email and password)

once you have done that from the run menu run the retrieveAutoPI. This should get you started

function retrieveAutoPI() {
let authData = retrieveToken();
if (authData){
let bbmData = retrieveData(authData.token,authData.user.devices[0].id,“obd.rpm.value”)
insertData(bbmData);
}

}

function retrieveToken()
{
let data = {“email”:“redacted_email”,“password”:“redacted_password”}
let url = “https://api.autopi.io/auth/login/”
let options = {
“method”: “post”,
“payload”: data
};
let response = UrlFetchApp.fetch(url, options);
if (response.getResponseCode() == 200){
return JSON.parse(response.getContentText());
} else
{
return null;
}

}

function retrieveData(token, device_id, field){
var now = new Date().toISOString();
var yesterday = new Date()
//yesterday.setHours(yesterday.getHours()-5)
yesterday.setDate(yesterday.getDate()-1)
yesterday = yesterday.toISOString()

var options = {
“method”: “get”,
“headers”: {
“Authorization”: "bearer " + token
},
};

var url = “https://api.autopi.io/logbook/storage/read/”+
“?from_utc=”+yesterday+
“&device_id=”+device_id+
“&field_type=primitive”+
“&interval=5m”+
“&field=”+field+
“&page_num=0”
var response = UrlFetchApp.fetch(url, options);

var data = JSON.parse(response.getContentText())
return(data)

}

function insertData(data){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“test”);
sheet.getLast
sheet.clear();
var sheetRows = []

for(i = 0; i < data.length; i++){
row = []
row[0] = data[i].ts;
row[1] = data[i].value
sheetRows.push(row)
}
range = sheet.getRange(1, 1, sheetRows.length, sheetRows[0].length).setValues(sheetRows);
range.activate();
range.sort({column: 1, ascending: false});
}

1 Like
#7

I will add it to my GitHub repository tomorrow -

#8

Did this ever get put on github? The formatting on here isn’t translating correctly into google sheets.

#9

Hi,

JVTeam, no I had not but have now. apologies, I don’t have time to test this over the next 48 hours however will do over

1 Like
#10

Hello.
Don´t understand the the readme file. Can you explain in other way? How can i install?
Thanks for the help.

#11

Hi,

I will update the readme file however the essential steps

  1. Create a new google sheet
  2. Rename a tab (or add. a tab named) test
  3. Select the tools / script editor option
  4. Paste the code in from github
  5. Change the userid password
  6. Select run retrieveAutoPI()
  7. The test sheet should be populated

Let me know how you go and where it does not work for you

#12

Hello.
For me does not work. Give me the error TypeError: Cannot read property ‘length’ of undefined (linha 68, arquivo “Código”)
Line 68
range = sheet.getRange(1, 1, sheetRows.length, sheetRows[0].length).setValues(sheetRows);

Cant see what is worg…

#13

Hi,

The issue is that there is no data being returned from the call.

The good news is that the authentication is working however there are probably two reasons

  1. You don’t have rpm data ? Can you confirm you have a PID names rpm. I am pretty sure you will.

  2. The other potential problem is that the time frame is to short. Currently it is retrieving data of the last 24 hours. If you have not logged any data in 24 hours then this is probably the problem. You can test that by changing the 1 on line 32 (1 day ) to how many days you want 1 - 700 ?

Let me know how it goes

#14

Hello.

I’m sorry, my mistake. I was using it on my electric vehicle so I didn’t change the RMP field to another one. Using one that I already have data works with. Any way to complete the code in order to gather more obd data?
Thank you for your help.

#15

Hi,

Warning - I suggest you create a new sheet to test this

great to see you have been successful - I had only really intended this to be a proof of capability but happy to extend its capability over the next bit to include over PIDs. I have made a quick change which should allow you to have as many PIDs as you want (you will need to add them like const RPM = “rpm” and add them to the array (as per the code)

Over the next while I might see if I can make it more extensible, particularly so it will add to data rather than overwriting but at the moment it should bring back data (where it exists for the last 24 hours). You can make that window wider by changing the 1 (day) to more.

Let me know how you go

#16

Hello.

Just to say that the code work very good. No problem at all.

Thanks for the help.

#17

Hi,

Great to see that it worked. Hopefully over the next few days, I will add the capability to retrieve from the last record and cap the number of rows at a defined limit, this will make it easier to manage over the medium term. Let me know if there is any other functionality that you are keen to see

Chris