Query flux to influxdb v2 from Google Apps Script

InfluxDB 2
1

I am having difficulty forwarding flux queries to influxdb v2 from Google Apps Script. Are there any examples I can draw from for these queries?

I tried to transpose an example that works on LUA, but I always get the 400 error.

function queryInfluxDB() {
// Imposta l’URL dell’endpoint Flux di InfluxDB
var url = “https://XXXXXXX/api/v2/query?org=org01”;

// Crea una richiesta POST con il corpo JSON contenente la query Flux
var request = {
“method”: “POST”,
“muteHttpExceptions” : true,
“headers”: {
“Authorization”: “Token XXXXXXXXXXXX”,
// “Accept”: “application/csv”,
“Accept”: “application/json”,

// “Content-Type”: “application/json”
“Content-Type”: “application/vnd.flux”
},

"payload": JSON.stringify({
  "data": {
    "query": "from(bucket: 'org01casa01') | range(start: -1h, stop: 2h)"
  }
})

};

// Invia la richiesta a InfluxDB e ottieni la risposta
Logger.log("url " + url);
for(i in request) {
Logger.log(i + ": " + request[i]);
if (i == “headers”) {
for(j in request[i]) {
Logger.log(j + ": " + request[i][j]);
// for(k in request[i][j]) {
// Logger.log(k + ": " + request[i][j][k]);
// }
}
}
}

var response = UrlFetchApp.fetch(url, request);

Logger.log("response : " + response);

for(i in response) {
Logger.log(i + ": " + response[i]);
}

Logger.log("response.getResponseCode() : " + response.getResponseCode());
if (response.getResponseCode() === 200) {
// Converte la risposta JSON in un oggetto JavaScript
var responseData = JSON.parse(response.getContentText());

Logger.log(" duecento " + response.getResponseCode() + " " + responseData);
for(i in responseData) {
 Logger.log(i + ": " + responseData[i]);  
 for(j in responseData[i]) {
  Logger.log(j + ": " + responseData[i][j]); 
  for(k in responseData[i][j]) {
   Logger.log(k + ": " + responseData[i][j][k]); 
  } 
 }
}

}
else {
console.error(“Errore durante l’esecuzione della query Flux:”, response.getResponseCode());
}
}

2

@lorenzo.grigoletto I see an error in your Flux query. You’re currently using a pipe (|) between functions, but Flux uses what’s called a pipe-forward (|>):

"payload": JSON.stringify({
  "data": {
-    "query": "from(bucket: 'org01casa01') | range(start: -1h, stop: 2h)"
+    "query": "from(bucket: 'org01casa01') |> range(start: -1h, stop: 2h)"
  }
})

Do you get any errors back when you try to run this?

3

Thanks for the suggestion, I had forgotten to revert the modification made back to the original version.
I keep getting the error 400.
I am including some lines from the debug if they may be helpful:
Accept: application/json
Content-Type: application/vnd.flux
payload: {data:{query:from(bucket: ‘org01casa01’) |> range(start: -1h, stop: 2h)}}
response : {code:invalid,message:error in query specification while starting program: this Flux script returns no streaming data. Consider adding a \yield\ or invoking streaming functions directly, without performing an assignment}

4

Hello @lorenzo.grigoletto,
have you tried adding a |>yield() as the error suggests? In your query?
Is it possible you don’t have data available in that time range? Can you please check by increasing the time range as well?

5

image
image1366×768 118 KB

Thanks for the suggestion, but I have already tried inserting |> yield() and the data is there (a lot of it, too) as you can see from the screenshot.
I attach some information from the log:
Accept: application/json
Content-Type: application/vnd.flux
payload: {“data”:{“query”:"from(bucket: ‘org01casa01’) |> range(start: -1d, stop: 2h) |>yield() "}}
response : {“code”:“invalid”,“message”:“error in query specification while starting program: this Flux script returns no streaming data. Consider adding a "yield" or invoking streaming functions directly, without performing an assignment”}

6

I make many of these calls from a Fibaro HC3/Yubii machine where there is a LUA environment, and I have no problem.
I also have many iterations from Node Red without any difficulty.
Where can I find examples of calls from Google Apps Script?

7

Hello @lorenzo.grigoletto,
That’s very odd. Your query looks to be identical. Are you able to query any other databases? And v2 uses a unified API so it doesn’t make sense that you’re able to query within the UI with that query and not make the cuRL request.

Can you try:

curl --request POST \
  http://localhost:8086/api/v2/query?orgID=INFLUX_ORG_ID  \
  --header 'Authorization: Token INFLUX_TOKEN' \
  --header 'Accept: application/csv' \
  --header 'Content-type: application/vnd.flux' \
  --data "from(bucket: ‘org01casa01’) |> range(start: -1d, stop: 2h)'

please?

8

Good morning,
I tried this curl call from both windows 10 and Node Red and it works (I limited the range to reduce the number of occurrences extracted).
curl --request POST “https://XXXXXXX/api/v2/query?org=org01” --ssl-no-revoke --header “Authorization: XXXXXXXXXXXXX” --header “Accept: application/csv” --header “Content-type:application/vnd.flux” --data “from(bucket: "org01casa01") |> range(start: -10m, stop: 2m)”
Attached is the result of the call.
,result,table,_start,_stop,_time,_value,_field,_measurement
,_result,0,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:35:01Z,4.5,Temperature,MeteoEsterno
,_result,0,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:40:01Z,4.5,Temperature,MeteoEsterno

,result,table,_start,_stop,_time,_value,_field,_measurement,deviceID,deviceName
,_result,1,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:04Z,true,valuenew,OpeningSensorvalue,32,PFFinestraCameraJuve
,_result,1,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:42:17Z,false,valuenew,OpeningSensorvalue,32,PFFinestraCameraJuve
,_result,2,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:04Z,false,valueold,OpeningSensorvalue,32,PFFinestraCameraJuve
,_result,2,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:42:17Z,true,valueold,OpeningSensorvalue,32,PFFinestraCameraJuve

,result,table,_start,_stop,_time,_value,_field,_measurement,deviceID,deviceName
,_result,3,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:33:20Z,45.2,valuenew,PowerMetervalue,83,Potenza
,_result,3,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:36:55Z,45.1,valuenew,PowerMetervalue,83,Potenza
,_result,3,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:55Z,44.5,valuenew,PowerMetervalue,83,Potenza
,_result,4,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:33:20Z,44.8,valueold,PowerMetervalue,83,Potenza
,_result,4,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:36:55Z,45.2,valueold,PowerMetervalue,83,Potenza
,_result,4,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:55Z,45.1,valueold,PowerMetervalue,83,Potenza

,result,table,_start,_stop,_time,_value,_field,_measurement,deviceID,deviceName
,_result,5,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:35:04Z,uibutton1OnReleased,event1.data.actionName,QuickApp,132,InterfacciaInfluxdb
,_result,5,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:36:42Z,uibutton3OnReleased,event1.data.actionName,QuickApp,132,InterfacciaInfluxdb
,_result,5,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:39:17Z,uibutton3OnReleased,event1.data.actionName,QuickApp,132,InterfacciaInfluxdb
,_result,5,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:40:03Z,uibutton1OnReleased,event1.data.actionName,QuickApp,132,InterfacciaInfluxdb
,_result,5,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:54Z,uibutton3OnReleased,event1.data.actionName,QuickApp,132,InterfacciaInfluxdb
,_result,5,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:59Z,uibutton3OnReleased,event1.data.actionName,QuickApp,132,InterfacciaInfluxdb
,_result,6,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:35:41Z,SceneModifiedEvent,event1.type,Scene,100,QueryInfluxdbDATI
,_result,6,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:36:41Z,SceneModifiedEvent,event1.type,Scene,100,QueryInfluxdbDATI
,_result,6,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:36:42Z,SceneStartedEvent,event1.type,Scene,100,QueryInfluxdbDATI
,_result,6,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:39:17Z,SceneModifiedEvent,event1.type,Scene,100,QueryInfluxdbDATI
,_result,6,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:41:54Z,SceneModifiedEvent,event1.type,Scene,100,QueryInfluxdbDATI
,_result,7,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:35:00Z,SceneStartedEvent,event1.type,Scene,79,EstrazioneHC3UTC
,_result,7,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:40:00Z,SceneStartedEvent,event1.type,Scene,79,EstrazioneHC3UTC

,result,table,_start,_stop,_time,_value,_field,_measurement,deviceID,deviceName
,_result,8,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:44:22Z,24.8,valuenew,TemperatureSensorvalue,55,MovSalotto
,_result,9,2024-04-17T07:32:45.400047091Z,2024-04-17T07:44:45.400047091Z,2024-04-17T07:44:22Z,25,valueold,TemperatureSensorvalue,55,MovSalotto

C:\Users\User>

9

Morning,
I don’t know why, but the copy/past deletes the \ before the ".
So within the query you have to insert the \ before the " .