Use webhook to PUT data to google sheets?

NHN · October 30, 2016, 2:18am

Hi

I follow Google Sheets API instruction here
https://developers.google.com/sheets/guides/values

but I’m not familiar with HTTP method. Is it the right command I need to insert to Webhook Widget?

URL: https://sheets.googleapis.com/v4/spreadsheets/xxxxxMYIDxxxxx/values:batchUpdate
METHOD: POST
CONTENT TYPE: application/json
BODY: ???

and how to insert right information into BODY?
Example: Data need to insert data1, data2, data3 (/pin[0]/, /pin[1]/, /pin[2]/), and sheet range is A2, B2, C2

Sorry if I explain not clear, that because my English, hope someone help me

Costas · October 30, 2016, 3:12pm

@NHN read is GET and write is PUT, not POST. Is your Google Sheet private? If it is you also need to provide the API key.

Edit: actually all sheets look like they need the API key, private Sheets appear to require an OAuth 2.0 client ID.

To read cells A1:B1 of a public sheet this syntax works in a web browser:

https://sheets.googleapis.com/v4/spreadsheets/sheetID?includeGridData=true&ranges=A1%3AB2&key=API_KEY

and returns:

{
  "spreadsheetId": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "properties": {
    "title": "TestSheet",
    "locale": "en_GB",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "Europe/Helsinki",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false
      }
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      },
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "userEnteredValue": {
                    "stringValue": "Costas"
                  },
                  "effectiveValue": {
                    "stringValue": "Costas"
                  },
                  "formattedValue": "Costas",
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "arial,sans,sans-serif",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                },
                {
                  "userEnteredValue": {
                    "stringValue": "the coder."
                  },
                  "effectiveValue": {
                    "stringValue": "the coder."
                  },
                  "formattedValue": "the coder.",
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "arial,sans,sans-serif",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT"
                  }
                }
              ]
            }
          ],
          "rowMetadata": [
            {
              "pixelSize": 21
            },
            {
              "pixelSize": 21
            }
          ],
          "columnMetadata": [
            {
              "pixelSize": 100
            },
            {
              "pixelSize": 100
            }
          ]
        }
      ]
    }
  ]
}

Unfortunately the Webhook doesn’t seem to like the includeGridData=true part of the url. It might be because the json stream is just too big but I suspect it is an issue with Google’s API.
It works without this part of the url but as you will see from the Serial Monitor details below it doesn’t actually give you A1:B2 !!!

{
  "spreadsheetId": "**********************************",
  "properties": {
    "title": "TestSheet",
    "locale": "en_GB",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "Europe/Helsinki",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false
      }
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": 0,
        "title": "Sheet1",
        "index": 0,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    }
  ]
}

aquarius · October 30, 2016, 9:33pm

Hi,
I also tried to find a solution like this method.
See this page: https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/append
You can try how it works. Go to the bottom of this page to “Try it” section.
Fill the textboxes in like you see in this picture. Use your own Google spreadsheet ID. (You can find this at the web link row of your spreadsheet.) After accepted OAuth2.0 identification click on Execute button and the values will be added your spreadsheet.

I think it is a good starting point but not the final solution.
So it works but I don’t know how can I implement it to my Blynk webhook.

Regards,
aquarius

Costas · October 30, 2016, 11:54pm

@aquarius your link was useful as it gives a nice concise output from a web browser for reading cells:

{
  "range": "Sheet1!A1:B1",
  "majorDimension": "ROWS",
  "values": [
    [
      "Costas",
      "the coder."
    ]
  ]
}

from a url in the fomat of https://sheets.googleapis.com/v4/spreadsheets/sheet_ID/values/A1%3AB1?majorDimension=ROWS&valueRenderOption=UNFORMATTED_VALUE&key=api_key

Unfortunately it requires the Google username and password so it’s not working in Webhooks.

NHN · October 31, 2016, 1:13am

Thks you all, it’s required authorized so it couldn’t possible with webhook now. My project is store data collect from wiegand door access control, there are about 60 - 70 record ID. Hardward get ID from wiegand reader, than get Name and Room from that ID. Now, hardware can push data to Google Sheets and Table Widget to view and store records, my ideas is creating a Sheet with ID assign to row number, Name in Columm A, Door in Columm B, with google Api (if possible) I can easy to get Name and Door back to hardware and push to Table widget. I tried to use Json or something like Database, but I think it’s quite a complicate, so my solution now is use SD card to creat a text file name by ID (ID.txt) that store Name and Door, it’s easy to read out or change or create Name and Door by ID from Blynk app. By the way, because I’m started to code and electronic about 6 - 7 months, there are many things I couldn’t know and understand, I’m very appriciate if someone can help me.

Costas · October 31, 2016, 9:53am

I can now read the cells of public Google sheets with the Webhooks widget using the following syntax:

https://sheets.googleapis.com/v4/spreadsheets/sheetID/values/A1%3AB1?key=api_key

This gives the following in Serial Monitor or Terminal

{
  "range": "Sheet1!A1:B1",
  "majorDimension": "ROWS",
  "values": [
    [
      "Costas",
      "the coder."
    ]
  ]
}

Data size: 124

With a bit more effort I’m sure it would be possible to add data to the public sheet with the Webhooks widget and for many applications this would be fine. It doesn’t really matter if the public knows what temperature it is in your bedroom or when you last switched on the Central Heating. You can restrict sheets to view only so the public wouldn’t be able to change the data but I suspect Webhooks would then not have the facility to update the sheet with new data (something I might check later).

Google appear to currently have a V3 and V4 of the API and they work slightly differently and presumably V3 will be retired at some future date.

OAuth2.0 identification for private sheets looks quite complex and one way involves making a DNS txt record on the domain making the Webhook call. This is not practical for the Blynk cloud server but should be possible on your own cloud server.

@NHN your requirement of storing private data doesn’t look to be easily achieved with Google sheets. I’m not really a fan of storing data on SD cards but I guess it is a workable solution. If you are using an ESP in your project most of them have between 3MB and 15MB of data storage available to you.

I guess the best solution is to have the data stored on a secure server that has an easier API to authenticate via the Webhook widget than Google sheets.

aquarius · October 31, 2016, 11:02am

Hi,
may I found another way.
I used Zapier webservice for a long time ago and my idea is take over data handling to Zapier.
Zapier can handle Google authentication. I have tested it some minutes ago with offline Google account and it was succesful!
More info: https://zapier.com/blog/how-use-zapier-webhooks/

Step1: Create a Zapier account, log in
Step2: Explore this Zap:

Step3: Click on “Use this Zap”
Step4: Go through the following steps. Use the generated custom webhook URL for testing on the next page (Zapier waiting for this): https://resttesttest.com/
Step5: Fill textboxes and click on “Ajax request” button

Step6: Continue with the spreadsheet parameters in Zapier
Step7: If the test was succesful, click on “Finish”
Thats all!

May we can use the generated URL in Blynk. Somebody can test it?
(I can not test it today, may tomorrow.)

Regards,
aquarius

NHN · October 31, 2016, 11:35am

Thks @Costas for your informations, I tried your way, but not success, can you explain more details. I created API key, my sheets go to public, but when I try on web browser, reponse is not authorized

Costas · October 31, 2016, 11:37am

@NHN show me the format of the url you are using.

NHN · October 31, 2016, 11:47am

@Costas here
https://sheets.googleapis.com/v4/spreadsheets/19EIXWPDoUGEAHyIjH2wgZbyXSuDt4muyYyyqe1rA-As/values/DoorData!A2:F2&key=AIzaSyAdKxtSm0PTfbtsYI-fNKld0dCJ-yhr4_w

Costas · October 31, 2016, 11:54am

@NHN I am able to obtain these details from your URL:

{
“range”: “DoorData!A2:F2”,
“majorDimension”: “ROWS”,
“values”: [
[
“30/10/2016 20:54:32”,
“30/10/2016.20:54:26”,
“3”,
“TRUOC”,
“Nam”,
“123”
]
]
}

NHN · October 31, 2016, 11:56am

@Costas what’s a surprise, why I can not
here my response

{
“error”: {
“code”: 403,
“message”: “The request cannot be identified with a client project. Please pass a valid API key with the request.”,
“status”: “PERMISSION_DENIED”
}
}

Costas · October 31, 2016, 12:03pm

Try url encoding the cells so

DoorData!A2%3AF2?key=

not

DoorData!A2:AF2?key=

Let me know what that gives you and if it is still an error I will show you the steps I took.

NHN · October 31, 2016, 12:06pm

It worked
Thks @Costas so much
How about if I don’t public sheets? Let’s me try

Costas · October 31, 2016, 12:06pm

I tried with Zapier and before I messed it all up https://resttesttest.com/ was entering the details (only tried public sheet at this stage). However I couldn’t see where it was actually giving me a usable url other than the hooks.zapier… and it appears this would enter the same data over and over again (i.e. what you set up in https://resttesttest.com/).

Maybe I am missing something.

Costas · October 31, 2016, 12:18pm

I have tried and failed.

For public sheets the extra step I took to gain access to your sheet data and for you to also see the data without error is at Method: spreadsheets.values.get | Google Sheets | Google for Developers

Just enter spreadsheetId and range and then click Execute without OAuth. It will then give you the correct url encoding to use.

There are actually 3 types of sheets, public, private and other.

Other is between public and private and requires users to know the url and works with the regular API key.

So as the spreadsheetId is a 44 character code it is probably fairly safe to use it without others seeing the data.

NHN · October 31, 2016, 12:58pm

I think you are right. Public sheets isn’t totally unsecured.

Costas · October 31, 2016, 3:54pm

@aquarius I think I was misled by your screenshot of value1, value2, value3.

For set up purposes it appears those fields should be empty and then you populate them in the body of POST.

I’m making progress now.

Zapier does work with Blynk Webhook widget for private sheets and I have been able to add rows of data to an existing private sheet. It looks really cool watching the sheet update as you press a button in Blynk.

I now need to get my head around the /pin/ aspect of the body as manually entering the body as:

{"Date":"31/10/2016","Hardware":"ESP","Time":"17:00","ID":"147","Data":"27.20"}

is not really practical.

aquarius · October 31, 2016, 7:49pm

@Costas, it works for me too. The problem is the limitations of free account of Zapier. You have only 100 actions/month for free. It is probably enough logging a garage door opening action but not for logging a room temperature.
Next account option is 15USD/3000 action/month which is not too economical.
I think we have to find another way…

Costas · October 31, 2016, 7:50pm

20 USD for 1000 when I checked earlier today.