Extracting usefull subvariables from (google sheets) data through webhook

Hello everyone.

I have been attempting to control neopixels through cloud data (specifically Google Sheets). This has brought me to the intergration ‘Webhook’. This seems to correctly connect my Particle Photon to the cloud, however where I get stuck is how to convert the incoming data into something that I can use.

#include "Particle.h"

        

void setup() {
  // Subscribe to the integration response event
  Particle.subscribe("hook-response/Steps", myHandler, MY_DEVICES);
}
    

void loop() {
  // Get some data
  String data = String(10);
  // Trigger the integration
  Particle.publish("Steps", data, PRIVATE);
  // Wait 6 seconds
  delay(6000);
  }
      
          


void myHandler(const char *event, const char *data) {

  char StepsT=atoi(data);
  
 
}
          

This gives the following output into my console:
{“columns”:{"_cn6ca":[600,40,64,350,251,249,3000,5,23,750]},“rows”:[{"_cn6ca":600},{"_cn6ca":40},{"_cn6ca":64},{"_cn6ca":350},{"_cn6ca":251},{"_cn6ca":249},{"_cn6ca":3000},{"_cn6ca":5},{"_cn6ca":23},{"_cn6ca":750}]}

If I want to use these values 600,40,64,350,251,249,3000,5,23,750 how do I call these individual values in my code? I believe they are stored in the variable *data, but I am unable to figure out how to filter this variable into useable subvariables.

Thanks a lot in advance for helping me!

You can use this library to parse and extract field from JSON
https://build.particle.io/libs/JsonParserGeneratorRK/0.1.0/tab/example/1-parser-JsonParserGeneratorRK.cpp

1 Like

Where should I go to learn how to use this specific library? I am assuming I should utilize this library within the [quote=“TimM, post:1, topic:55355”]

void myHandler(const char *event, const char *data)

[/quote] This is my very first Particle project, so sorry if my questions are kind of dumb. The end goal is to use the data to control Neopixels (which I have been able to do without cloud connection).

Also thank you very much for your very quick and helpfull response!

When I have first contact with a library I don’t know (yet) I typically have a look at the provided examples and try to figure out what they do how and why.

So with the example I linked above you see a test2 string that would - in your - case be data.
And the portion of runTest() that is most applicable for your use case would be what needs to go into your subscription handler.

In your JSON you have a field columns which in turn contains a field _cn6ca which holds an integer array.

So after you have parsed your JSON you should be able to home in on that array step by step just playing and trying different things.
This is the best way to learn new things in a way to have them stick in your mind for future use :wink:

When you use Web IDE you often see this symbol image next to the library name which brings you to the GitHub repository which - in this case - also features some quite extensive explanation

1 Like

@TimM, how did you get on?

BTW, did you know that you can reduce the amount of data you get back from your webhook by means of a custom response template?

1 Like

So far Ive been tinkering with the example script. I had some struggles reading the serial.print lines, but downloaded Tera Term VT which seems to work well now. I havent been able yet to adapt the example script to run on different chars (like mine), but I do think this could offer a solution for me!

Thank you again!

1 Like

If you get stuck, I found some time to whip up some code that extracts your desired data.

I’ll post it here, but suggest you keep up your own research and only have a glimpse at it when you desperately need it :wink:

sample implementation

#include "JsonParserGeneratorRK.h"

const char * const testData = "{\"columns\":{\"_cn6ca\":[600,40,64,350,251,249,3000,5,23,750]},\"rows\":[{\"_cn6ca\":600},{\"_cn6ca\":40},{\"_cn6ca\":64},{\"_cn6ca\":350},{\"_cn6ca\":251},{\"_cn6ca\":249},{\"_cn6ca\":3000},{\"_cn6ca\":5},{\"_cn6ca\":23},{\"_cn6ca\":750}]}";

JsonParserStatic<2048, 100> jp;

void setup() {
  Particle.subscribe("test", testHandler, MY_DEVICES);
  Serial.begin(9600);
}

void loop() { }


void testHandler(const char* event, const char* data) {
  // when no data was sent, let's send some to ourselves
  if (!strlen(data)) {
    Particle.publish("test", testData, PRIVATE);
    return;
  }
  
  // step-by-step parsing 
  jp.clear();                                                               // remove any residual data from previous calls
  jp.addString(data);                                                       // inject the new data
  
  if (!jp.parse()) {                                                        // try to parse that data
    Serial.println("parse failed");
    return;
  }

  const JsonParserGeneratorRK::jsmntok_t *columns;
  if (!jp.getValueTokenByKey(jp.getOuterToken(), "columns", columns)) {     // extract the "columns" object from the root object
    Serial.println("columns not found");
    return;
  }
  
  const JsonParserGeneratorRK::jsmntok_t *colArray;
  if (!jp.getValueTokenByIndex(columns, 1, colArray)) {                     // extract the first object in the "columns" object (assuming it's our array)
    Serial.println("columns array not found");
    return;
  }

  for (int i=0; i < jp.getArraySize(colArray); i++) {                       // iterate over the array
    int val;
    jp.getValueByIndex(colArray, i, val);                                   // get the data (assuming it's and int)
    Serial.printlnf("%d: %d", i, val);                                      
  }
  Serial.println();
}
1 Like