Arduino IoT Cloud Google Sheets Integration

Hey everyone, For the first time, I'm able to share a resolution to arduino challenge. I too have been combing the web for tutorials, workarounds on how to send data from my MKR1010 to Google Spreadsheet. I went down a week worth of a rabbit hole, setting up, deleting, debugging and getting stuck every time. I'm a newbie at this Arduino stuff, but from all the information I've read and tried, Google Cloud Platform now has additional security guidelines that must be met in order to GET/POST Json request on the Google Platform. I, MKR1010 boards, nor arduino geniuses have yet to figured out how to code, arrange code and/or register our boards with the necessary authentication/credentials (Oauth2.0) to get the Board to talk to the Google Cloud. I FOUND A WORK AROUND Follow this recently posted tutorial that uses IFTTT to bridge data collection and dispersal to a email. I was able to get it to send to my google sheets. GOOD LUCK. https://support.arduino.cc/hc/en-us/articles/360013896199-How-to-integrate-the-Arduino-IoT-with-IFTTT Nevertheless, If someone knows of a tutorial that can send MKR1010 data to a google sheet without third party use- Please inform us!

Board: Arduino MKR GSM 1400 This also doesnt work for me; even after fiddeling around with many different approaches, that have been posted in the comments. I dont understand the doGET() command even after reading through Googles documentation, but from the reports this could be the problem.

Hello Marco, Great project! I've got everything working but I can't get any data in the google sheet. I can see my data coming in the "Arduino IoT Cloud"-website. My webhook is in order and I use the correct Thing ID but no data is received in the google sheet. I also tried the possible solutions already suggested above (by jld13) but still no succes. Any idea what to check to get things working? Thanks in advance, Bart

Hi Marco, thanks for this great project. It avoided many pitfalls I had with the original integration of a web hook using IFTTT (which does not support filter script in their free plan) - where it was difficult to support multiple sensor values and add them each in their own column in the google sheet. However I still suggest several changes to your Google Appscript that I have posted here https://github.com/arduino/arduino-iot-google-sheet-script/issues/4 Thanks

Hello, this is a great project and I want to use it to collect temperature data from a temperature monitor thing. I followed this tutorial but can not get it to work properly. Could you verify if the given examples still works?

I couldn't implement it. I have an error in "var cloudData = JSON.parse(e.postData.contents)" line and I don't have idea because I'm not professional programmer. Can anyone help me to resolve it?

Hi! I was not able to get the sheet updated as it should be, I am trying to adjust the communication time as it was based on the wifi signal. Or is this feature overall is disabled if I am using the IoT free plan instead of the maker plan? I have been looking up other tutorials on importing JSON data, and found one (https://www.youtube.com/watch?v=EXKhVQU37WM) that requires to manipulate not only the script editor but the excel sheet cell function (i.e. =importjson(...)), please advise if that is necessary. Thanks in advance!

Hi Marco, This is a great project.Unfortunately, I also ran into a problem with a blank sheet. I found that the getSheetByName function doesn't seem to call my sheet appropriately. My app was definitely published correctly. I am also sure that my sheet is in the root directory of the google folder structure. I was able to get around this by commenting out the getActiveSheet and getSheetByName commands and reverting back to some code that Stephen Borsay included in his 'Send MKR1000 Data to Google Sheets' project. He suggested copying the URL directly between the /d and /edit components of the URL. See this link for further discussion: https://create.arduino.cc/projecthub/detox/send-mkr1000-data-to-google-sheets-1175ca Here are the modifications that I made: // get active spreasheet // var ss = SpreadsheetApp.getActiveSheet(); // get sheet named RawData // var sheet = ss.getSheetByName('RawData'); var id = '<YOUR_SPREADSHEET_URL_HERE';//docs.google.com/spreadsheetURL/d var sheet = SpreadsheetApp.openById(id).getActiveSheet(); If you have any thoughts why this isn't working for me, I would appreciate it. I like the style of the original code. Perhaps, it has something to do with an incomplete filename or root directory structure lacking from the name? I also thought it might be a permissions issue, but I opened the file up without success. Thanks again for your great project. Alex

HII... I have made one project to test a circuit. there are 10 numbers of tests. I want to take this data of each test along with a barcode so that I do have documentation of circuit testing. can I use the same procedure?

Hi Marco ! It is posible to use ESP8266F in this case ?

Ciao Marco, purtroppo sul foglio di calcolo non mi appare niente. Come faccio a vedere che IoT cloud manda dati tramite il web-Hook? Grazie mille Domenico

Ciao, una domanda, ma bisogna cliccare sul pulsante "esegui" dello script di Google?

Does it work with Arduino-Device Manager, especially for LoRaWAN generic devices? Has it been tested? Thanks :)

Is there any way to change one of the properties' value using this style of webhooks?

Hi! Really cool project and something I want to implement in one of my own. However, I don't get any data in my spreadsheet and when I try to debug the Google Script, I get the following error message: "TypeError: Cannot read property "postData" from undefined. (line 33, file "Code")" It is in the "doPost(e) {}" function in the line var cloudData = JSON.parse(e.postData.contents); Do I have to define the JSON object "cloudData" somewhere else?

This used to work beautifully. Unfortunately, it looks like Google has made it impossible to use this approach, There is more security now. I have had it working, and then it just stopped - same code, same google sheet, but google rejects the request.

I have the Arduino MKR 1010, I follow the tutorial but it doesn't work. In my case, I have to turn on a DC motor with a buttom and everytime I push the buttom this data should be send in the Google Sheet, any suggestions?

hi all, thanks for sharing the code, it didn't work for me right away, but after editing var ss = SpreadsheetApp.getActiveSpreadsheet(); and control of the year if (date.getFullYear() > 2018) { .... get.year returns the number of years since 1900, i.e. 122 now everything works perfectly for me :)

Hi, thank for sharing all this project ! I managed to get it work some months ago, but it doesn't work anymore... The sheet remains dead... I tested the google WebHook by typing it directly in my browser url tab : https://script.google.com/macros/s/<GOOGLE-SCRIPT-ID>/exec?val=333 "val" is my property name. I used to work back in November... But now, I get the error "Fonction de script introuvable : doGet" in my french browser, we can translate by : "Script function not found: doGet". I tried so hard to get this working in the past few day (we are confined because of the Covid-19 in France). Can you help me, please ? Thanks again for all this great work ! Rémi

As it seems from google they demand now that the google script be validated by them and more cyber security demands (and they are right) so this mechanism to connect to google assets seems not to be working at all

HII... I have made one project to test a circuit. there are 10 numbers of tests. I want to take this data of each test along with a barcode so that I do have documentation of circuit testing. can I use the same procedure?

Hi Marco ! It is posible to use ESP8266F in this case ?

Hi! Really cool project and something I want to implement in one of my own. However, I don't get any data in my spreadsheet and when I try to debug the Google Script, I get the following error message: "TypeError: Cannot read property "postData" from undefined. (line 33, file "Code")" It is in the "doPost(e) {}" function in the line var cloudData = JSON.parse(e.postData.contents); Do I have to define the JSON object "cloudData" somewhere else?

Hi Marco, This is a great project.Unfortunately, I also ran into a problem with a blank sheet. I found that the getSheetByName function doesn't seem to call my sheet appropriately. My app was definitely published correctly. I am also sure that my sheet is in the root directory of the google folder structure. I was able to get around this by commenting out the getActiveSheet and getSheetByName commands and reverting back to some code that Stephen Borsay included in his 'Send MKR1000 Data to Google Sheets' project. He suggested copying the URL directly between the /d and /edit components of the URL. See this link for further discussion: https://create.arduino.cc/projecthub/detox/send-mkr1000-data-to-google-sheets-1175ca Here are the modifications that I made: // get active spreasheet // var ss = SpreadsheetApp.getActiveSheet(); // get sheet named RawData // var sheet = ss.getSheetByName('RawData'); var id = '<YOUR_SPREADSHEET_URL_HERE';//docs.google.com/spreadsheetURL/d var sheet = SpreadsheetApp.openById(id).getActiveSheet(); If you have any thoughts why this isn't working for me, I would appreciate it. I like the style of the original code. Perhaps, it has something to do with an incomplete filename or root directory structure lacking from the name? I also thought it might be a permissions issue, but I opened the file up without success. Thanks again for your great project. Alex

This used to work beautifully. Unfortunately, it looks like Google has made it impossible to use this approach, There is more security now. I have had it working, and then it just stopped - same code, same google sheet, but google rejects the request.

Hi Marco, thanks for this great project. It avoided many pitfalls I had with the original integration of a web hook using IFTTT (which does not support filter script in their free plan) - where it was difficult to support multiple sensor values and add them each in their own column in the google sheet. However I still suggest several changes to your Google Appscript that I have posted here https://github.com/arduino/arduino-iot-google-sheet-script/issues/4 Thanks

Board: Arduino MKR GSM 1400 This also doesnt work for me; even after fiddeling around with many different approaches, that have been posted in the comments. I dont understand the doGET() command even after reading through Googles documentation, but from the reports this could be the problem.

Ciao, una domanda, ma bisogna cliccare sul pulsante "esegui" dello script di Google?

Ciao Marco, purtroppo sul foglio di calcolo non mi appare niente. Come faccio a vedere che IoT cloud manda dati tramite il web-Hook? Grazie mille Domenico

As it seems from google they demand now that the google script be validated by them and more cyber security demands (and they are right) so this mechanism to connect to google assets seems not to be working at all

Does it work with Arduino-Device Manager, especially for LoRaWAN generic devices? Has it been tested? Thanks :)

Hello Marco, Great project! I've got everything working but I can't get any data in the google sheet. I can see my data coming in the "Arduino IoT Cloud"-website. My webhook is in order and I use the correct Thing ID but no data is received in the google sheet. I also tried the possible solutions already suggested above (by jld13) but still no succes. Any idea what to check to get things working? Thanks in advance, Bart

I have the Arduino MKR 1010, I follow the tutorial but it doesn't work. In my case, I have to turn on a DC motor with a buttom and everytime I push the buttom this data should be send in the Google Sheet, any suggestions?

I couldn't implement it. I have an error in "var cloudData = JSON.parse(e.postData.contents)" line and I don't have idea because I'm not professional programmer. Can anyone help me to resolve it?

Hi, thank for sharing all this project ! I managed to get it work some months ago, but it doesn't work anymore... The sheet remains dead... I tested the google WebHook by typing it directly in my browser url tab : https://script.google.com/macros/s/<GOOGLE-SCRIPT-ID>/exec?val=333 "val" is my property name. I used to work back in November... But now, I get the error "Fonction de script introuvable : doGet" in my french browser, we can translate by : "Script function not found: doGet". I tried so hard to get this working in the past few day (we are confined because of the Covid-19 in France). Can you help me, please ? Thanks again for all this great work ! Rémi

Hey everyone, For the first time, I'm able to share a resolution to arduino challenge. I too have been combing the web for tutorials, workarounds on how to send data from my MKR1010 to Google Spreadsheet. I went down a week worth of a rabbit hole, setting up, deleting, debugging and getting stuck every time. I'm a newbie at this Arduino stuff, but from all the information I've read and tried, Google Cloud Platform now has additional security guidelines that must be met in order to GET/POST Json request on the Google Platform. I, MKR1010 boards, nor arduino geniuses have yet to figured out how to code, arrange code and/or register our boards with the necessary authentication/credentials (Oauth2.0) to get the Board to talk to the Google Cloud. I FOUND A WORK AROUND Follow this recently posted tutorial that uses IFTTT to bridge data collection and dispersal to a email. I was able to get it to send to my google sheets. GOOD LUCK. https://support.arduino.cc/hc/en-us/articles/360013896199-How-to-integrate-the-Arduino-IoT-with-IFTTT Nevertheless, If someone knows of a tutorial that can send MKR1010 data to a google sheet without third party use- Please inform us!

Hello, this is a great project and I want to use it to collect temperature data from a temperature monitor thing. I followed this tutorial but can not get it to work properly. Could you verify if the given examples still works?

Is there any way to change one of the properties' value using this style of webhooks?

Hi! I was not able to get the sheet updated as it should be, I am trying to adjust the communication time as it was based on the wifi signal. Or is this feature overall is disabled if I am using the IoT free plan instead of the maker plan? I have been looking up other tutorials on importing JSON data, and found one (https://www.youtube.com/watch?v=EXKhVQU37WM) that requires to manipulate not only the script editor but the excel sheet cell function (i.e. =importjson(...)), please advise if that is necessary. Thanks in advance!