# Google Sheets - Script

In firmware a define in [SCI.h](https://github.com/ValetronSystems/VALTRACK-V4-ESP32-C3/blob/master/main/include/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.

```c
#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"}]}
```

{% file src="<https://3503701112-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDviKs2J6kRDnmAGBdimD%2Fuploads%2FMyKOPNPfNFHPb2s33NWh%2Flog.xlsx?alt=media&token=dbf10866-252f-44ea-a2db-a2ea2a3342b3>" %}
Sample file with headers
{% endfile %}

### Script to be pasted in Google sheets extension

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

```