Google Sheets - Script

App script info to append VALTRACK-V4 HTTP post data to Google sheets

In firmware a define in SCI.h has to be enabled, which makes the HTTP function wait for 302 Moved Temporarily response. Google App script gives 302 response instead of 200 so enable this #define. When you want to use custom response string, you can comment this line.

#define SHEETS_ENABLED

Sample packet to test

{"resource":[{"devid":"123095050300182","time":"2023-03-29 12:30:44","etype":"REBOOT","lat":"15.200000","lon":"75.32000","vbat":"0.000000","speed":"0.000000"}]}

5KB

log.xlsx

Script to be pasted in Google sheets extension

function doPost(req) {
  
  var logid=0;
  var devid=0;
  var lat=0;
  var lon=0;
  var time=0;
  var server_time=0;
  var etype=0;
  var speed=0;
  var vbat=0;
  var vmbat=0;
  var pInt=0;
  var nlat=0;
  var nlon=0;
  var ncsq=0;
  var ltype=0;
  var coach_num=0;
  var fuel=0;
  var origin=0;
  var engine=0;

  var output;
  var data = JSON.parse(req.postData.contents);
  var sheet = SpreadsheetApp.getActiveSheet();
  
  let datenow = new Date();
  
  var packet = data.resource;
  for(var i=0;i<packet.length;i++)
  {


    try{
        
        sheet.appendRow([
          
          packet[i].logid,
          packet[i].devid,
          packet[i].lat,
          packet[i].lon,
          packet[i].time,
          datenow,
          packet[i].etype,
          packet[i].speed,
          packet[i].vbat,
          packet[i].vmbat,
          packet[i].pInt,
          'http://maps.google.com/maps?z=18&q='+packet[i].lat+','+packet[i].lon,
          packet[i].nlat,
          packet[i].nlon,
          packet[i].ncsq,
          packet[i].ltype,    
          packet[i].coach_num,
          packet[i].fuel,
          packet[i].origin,
          packet[i].engine,
          'VALTRACK-V4-VTS-ESP32-C3'
          
          
          ]);
    }
    catch(error)
    {
      output = error;

    }
      
  }  
  return ContentService.createTextOutput(JSON.stringify({data:output})).setMimeType(ContentService.MimeType.JSON);
}