Save Arduino Yún data to a spreadsheet


We'll show you how to make your Arduino Yún add rows of data to a Google spreadsheet. You can use this to log data from sensors connected to your Yún, like temperature readings over time, or other instances where your Yún is receiving information you'd like to record.

This sketch uses our Google > Sheets > AppendValues Choreo.

Get Set Up

1Make sure you have a Temboo account. If you don't already have one, you can register for a free account here.

2Since this sketch uses a Google spreadsheet, you'll also need a Google account.

3Login to Google's Developer Console, and create a new Project if you haven't done so already.

4Under the API Manager section, select Library and make sure you've enabled API Access for the Google Sheets API.

5Select Credentials in the API Manager section, and create a new Client ID specifying Web application for the Application Type.

6When configuring the Consent Screen, make sure you fill out the Email Address and Product Name fields.

7Save the Consent Screen details and specify the callback URL below as the Authorized Redirect URI. Make sure to replace ACCOUNT_NAME with your Temboo account name.

https://ACCOUNT_NAME.temboolive.com/callback/google

8 Go to the Google > OAuth > InitializeOAuth Choreo page, and fill in the Client ID from the app you registered at Google and the following Scope: https://www.googleapis.com/auth/spreadsheets. Then click Generate Code to run the Choreo from our site.

Google OAuth Inputs

The InitializeOAuth choreo will return an authorization URL and a callback ID (required for the FinalizeOAuth step).

9Open a new web browser, navigate to the authorization URL returned by the InitializeOAuth Choreo, and click "Accept" to grant the app access to your Google account.

Google OAuth Accept

10Go to the Google > OAuth > FinalizeOAuth Choreo page, and specify the callback ID returned earlier by the InitializeOAuth Choreo. Then click Generate Code to run the Choreo from our site. This process will return a Refresh Token which can be used along with the Client ID and Client Secret to authenticate with Google.

11Create a Google Spreadsheet. In this example our spreadsheet has two columns as seen below: time (in milliseconds) and sensor values.

Google Spreadsheet

A screenshot of a spreadsheet taking data from a Yún - note the column names

12When viewing your spreadsheet, you'll see a spreadsheet ID in your browser's URL bar. Copy this ID because you'll need it when running the AppendValues Choreo. In the example below, the highlighted part is the Spreadsheet ID.

Google Spreadsheet

13Make sure that you have the latest version of the Arduino IDE. You should also be sure that you have the newest version of the Temboo Library by checking the Arduino Library Manager

14Make sure that your Yún is connected to the Internet. Arduino has a helpful guide if you need assistance.

Write the Sketch

Copy the sketch code below into a new tab in your Arduino IDE. This code calls the AppendValues Choreo, and you will need to replace the placeholder values in the code with your own Google account details and the ID of your Google Spreadsheet.

/*
  SendDataToGoogleSpreadsheet

  Demonstrates appending a row of data to a Google spreadsheet from the Arduino Yun 
  using the Temboo Arduino Yun SDK.  

  This example code is in the public domain.
*/

#include <Bridge.h>
#include <Temboo.h>
#include "TembooAccount.h" // contains Temboo account information

/*** SUBSTITUTE YOUR VALUES BELOW: ***/

// Note that for additional security and reusability, you could
// use #define statements to specify these values in a .h file.

const String GOOGLE_CLIENT_ID = "your-google-client-id";
const String GOOGLE_CLIENT_SECRET = "your-google-client-secret";
const String GOOGLE_REFRESH_TOKEN = "your-google-refresh-token";

// The ID of the spreadsheet you want to send data to
// which can be found in the URL when viewing your spreadsheet at Google. For example, 
// the ID in the URL below is: "1tvFW2n-xFFJCE1q5j0HTetOsDhhgw7_998_K4sFtk"
// Sample URL: https://docs.google.com/spreadsheets/d/1tvFW2n-xFFJCE1q5j0HTetOsDhhgw7_998_K4sFtk/edit
const String SPREADSHEET_ID = "your-spreadsheet-id";

int numRuns = 1;   // execution count, so this doesn't run forever
int maxRuns = 100;   // the max number of times the Google Spreadsheet Choreo should run

void setup() {
  
  // for debugging, wait until a serial console is connected
  Serial.begin(9600);
  delay(4000);
  while(!Serial);

  Serial.print("Initializing the bridge... ");
  Bridge.begin();
  Serial.println("Done!\n");
}

void loop()
{

  // while we haven't reached the max number of runs...
  if (numRuns <= maxRuns) {

    Serial.println("Running AppendValues - Run #" + String(numRuns++));

    // get the number of milliseconds this sketch has been running
    unsigned long now = millis();
    
    Serial.println("Getting sensor value...");

    // get the value we want to append to our spreadsheet
    unsigned long sensorValue = getSensorValue();

    Serial.println("Appending value to spreadsheet...");

    // we need a Process object to send a Choreo request to Temboo
    TembooChoreo AppendValuesChoreo;

    // invoke the Temboo client
    // NOTE that the client must be reinvoked and repopulated with
    // appropriate arguments each time its run() method is called.
    AppendValuesChoreo.begin();
    
    // set Temboo account credentials
    AppendValuesChoreo.setAccountName(TEMBOO_ACCOUNT);
    AppendValuesChoreo.setAppKeyName(TEMBOO_APP_KEY_NAME);
    AppendValuesChoreo.setAppKey(TEMBOO_APP_KEY);
    
    // identify the Temboo Library choreo to run (Google > Sheets > AppendValues)
    AppendValuesChoreo.setChoreo("/Library/Google/Sheets/AppendValues");
    
    // set the required Choreo inputs
    // see https://www.temboo.com/library/Library/Google/Sheets/AppendValues/ 
    // for complete details about the inputs for this Choreo
    
    // your Google Client ID
    AppendValuesChoreo.addInput("ClientID", GOOGLE_CLIENT_ID);

    // your Google Client Secret
    AppendValuesChoreo.addInput("ClientSecret", GOOGLE_CLIENT_SECRET);

    // your Google Refresh Token
    AppendValuesChoreo.addInput("RefreshToken", GOOGLE_REFRESH_TOKEN);

    // the title of the spreadsheet you want to append to
    AppendValuesChoreo.addInput("SpreadsheetID", SPREADSHEET_ID);

    // convert the time and sensor values to a json array
    String rowData = "[[\"" + String(now) + "\", \"" + String(sensorValue) + "\"]]";

    // add the RowData input item
    AppendValuesChoreo.addInput("Values", rowData);

    // run the Choreo and wait for the results
    // The return code (returnCode) will indicate success or failure 
    unsigned int returnCode = AppendValuesChoreo.run();

    // return code of zero (0) means success
    if (returnCode == 0) {
      Serial.println("Success! Appended " + rowData);
      Serial.println("");
    } else {
      // return code of anything other than zero means failure  
      // read and display any error messages
      while (AppendValuesChoreo.available()) {
        char c = AppendValuesChoreo.read();
        Serial.print(c);
      }
    }

    AppendValuesChoreo.close();
  }

  Serial.println("Waiting...");
  delay(5000); // wait 5 seconds between AppendValues calls
}

// this function simulates reading the value of a sensor 
unsigned long getSensorValue() {
  return analogRead(A0);
}

Create Your Header File

The sketch above references the TembooAccount.h header file, which contains your Temboo account information.

If you are currently logged in, you'll see your Temboo details in the code snippet below (otherwise you'll see placeholder values). Copy this code into a new tab in Arduino and call it TembooAccount.h.

#define TEMBOO_ACCOUNT "ACCOUNT_NAME"  // your Temboo account name 
#define TEMBOO_APP_KEY_NAME "APP_NAME"  // your Temboo app key name
#define TEMBOO_APP_KEY  "APP_KEY"  // your Temboo app key

With both files in place you are ready to upload the sketch and start adding data to your spreadsheet. Time to get logging!

Convert the sketch to work with the Yún Shield

If you're working with the Yún Shield paired with another Arduino board, you'll need to make some small changes to the Yún sketch above it so that it's compatible with the Arduino Yún Shield.

1First, change the include statement #include <Temboo.h> to #include <TembooYunShield.h>. Your include statements should look like this:

#include <Bridge.h>
#include <TembooYunShield.h>
#include "TembooAccount.h" 

2Next, change the Temboo object name from TembooChoreo to TembooYunShieldChoreo. Your code should look like this:

TembooYunShieldChoreo  AppendValuesChoreo;

Now your code is ready to run on your Yún Shield!

What's Next?

Now that you've mastered working with Google Spreadsheets, why not check out the rest of the 2000+ Choreos in our Library and get inspired for your next project.

Need Help?

We're always happy to help. Just email us at support@temboo.com, and we'll answer your questions.


Back