​

Log data to Google spreadsheets

In this tutorial we will show how to send data from an ESP32 to a Google Spreadsheet.

Prerequisites

We assume that you have set up your development environment as described in the IDE tutorial.

We also assume that you have flashed your device with Jaguar and that you are familiar with running Toit programs on it. If not, have a look at the Hello world tutorial.

Note that you can do this tutorial without a device. In that case, you need to use the -d host option whenever you invoke jag run. The program will then run on your computer instead of on a device.

You will need a Google account to do this tutorial.

Prepare the Google spreadsheet

First, we need to create a Google spreadsheet to log the data to. We will prepare the sheet to log timestamps, temperature and humidity. If you want to log other data, you can of course change this.

  1. Go to Google Sheets.
  2. Click on the + button to create a new spreadsheet.
  3. Name the spreadsheet ESP32 data.
  4. In the first row, enter the following column headers: Timestamp, Temperature, Humidity.
  5. Open the Extensions menu and select Apps Script.
  6. Give the project a name, for example ESP32 data logger.
  7. In the editor that opens, replace the default code with the following:
    /*
OnlyCurrentDoc
*/
function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = JSON.parse(e.postData.contents);
  sheet.appendRow([new Date(data.timestamp), data.temperature, data.humidity]);
  return ContentService.createTextOutput("OK");
}
  8. Optionally add the following doGet function. This will allow you to test the script with a browser.
    function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = JSON.parse(e.parameter.data);
  sheet.appendRow([new Date(data.timestamp), data.temperature, data.humidity]);
  return ContentService.createTextOutput("OK");
}
  9. Save the project.
  10. Click on the Deploy button and select New deployment.
  11. Select Web app under "Select type".
  12. Optionally add a description, for example "Initial deployment".
  13. Under "Who has access to the app", select `Anyone".
  14. Click on the Deploy button.
  15. Authorize the app to access your Google account.
  16. Copy the URL that is displayed in the dialog.
  17. Click on the OK button to close the dialog.

If you ever lose the URL, you can find it again by opening the Manage Deployments dialog. To do this, click on the Deploy button and select Manage deployments. The URL is displayed in the dialog.

Testing the script with the browser

If you added the doGet function, you can test your Google spreadsheet setup with a browser.

Take the URL you received in the previous section and append the following to it:

?data={"timestamp":123456789,"temperature":25.0,"humidity":50.0}

The complete URL should now look like this:

https://script.google.com/macros/s/<SOME_ID>/exec?data={"timestamp":123456789,"temperature":25.0,"humidity":50.0}

Open this URL in your browser. You should see the text OK displayed, and the data should be added to the spreadsheet.

You can simply delete the row from the spreadsheet to remove the data.

Toit program

Now that we have a spreadsheet to log the data to, we can write the Toit program that will send the data to it.

Packages

The HTTP functionality is not part of the core libraries and must be imported as a package. See the packages tutorial for details.

We are using the http package. To install it, run the following command:

jag pkg install github.com/toitlang/pkg-http@v2

We will also use the certificate-roots package:

jag pkg install github.com/toitware/toit-cert-roots@v1

Code

Start a new Toit program sheets.toit and watch it with Jaguar.

Don't forget to replace <YOUR URL> with the URL you received from Google.

The program uses the post-json method on the HTTP Client class to send the data to the spreadsheet. The post-json method will automatically encode the data as JSON and set the Content-Type header to application/json.

The --no-follow-redirects option is used to prevent the HTTP client from following any redirects. The Google Apps Script returns a redirect to a different site. That site is known to have a very big certificate that might not fit into memory. By not following the redirect we avoid potential issues with the page and also avoid another HTTP request.

import certificate-roots
import http
import net

URL ::= "<YOUR URL>"

main:
  certificate-roots.install-common-trusted-roots
  network := net.open
  client := http.Client.tls network

  response := client.post-json --no-follow-redirects --uri=URL {
    "timestamp": Time.now.utc.to-iso8601-string,
    "temperature": 42.0,
    "humidity": 1013.25,
  }

  if response.status-code != 200 and response.status-code != 302:
    print "Request failed: $response.status-code $response.status-message"

  response.drain

For simplicity we simply post some dummy data to the spreadsheet. In a real application you would replace this with the actual data you want to log.

Timestamp

If your ESP32 hasn't synchronized with an NTP server, the timestamp will be incorrect (somewhere in the 1970s). See the date-time tutorial for details on how to synchronize the time.