Hackster is hosting Hackster Holidays, Ep. 6: Livestream & Giveaway Drawing. Watch previous episodes or stream live on Monday!Stream Hackster Holidays, Ep. 6 on Monday!
Stephen Borsay
Published © GPL3+

Send MKR1000 Data to Google Sheets

This project allows you to transmit DHT environmental data from your MKR1000 to your own customizable Google Sheet for data logging.

IntermediateFull instructions provided3 hours33,385
Send MKR1000 Data to Google Sheets

Things used in this project

Hardware components

Arduino MKR1000
Arduino MKR1000
×1
DHT11 Temperature & Humidity Sensor (4 pins)
DHT11 Temperature & Humidity Sensor (4 pins)
×1
Jumper wires (generic)
Jumper wires (generic)
×1
Breadboard (generic)
Breadboard (generic)
×1

Software apps and online services

Arduino IDE
Arduino IDE
Google Sheets
Google Sheets

Story

Read more

Custom parts and enclosures

other data

other wireless

Schematics

MKR1000 wired

Layout

Code

MKR1000 to Google Sheets via PushingBox using th DHT

Arduino
This Arduino Sketch pushes data via WiFI to pushing box for encryption then on to PushingBox to log that data to your Google Sheet
//-----------------------------------------------
//This sketch is combined from Adafruit DHT sensor and tdicola for dht.h library
// https://learn.adafruit.com/dht/overview
// https://gist.github.com/teos0009/acad7d1e54b97f4b2a88
//other Authors Arduino and associated Google Script:
//Aditya Riska Putra
//Ahmed Reza Rafsanzani
//Ryan Eko Saputro
//See Also:
//http://jarkomdityaz.appspot.com/
//
//ELINS UGM
//
//Modified for Hackster.io project for the MKR1000 
//by Stephen Borsay(Portland, OR, USA)
//Since Arduino can't https, we need to use Pushingbox API (uses http)to run 
//the Google Script (uses https). Alternatly use Ivan's SecureWifi encryption


#include <WiFi101.h>
#include "DHT.h"

#define DHTPIN 5    // what pin we're connected to, pin1 is 5th pin from end

// Uncomment whatever DHT sensor type you're using!
#define DHTTYPE DHT11  // DHT 11
//#define DHTTYPE DHT21  // DHT 21
//#define DHTTYPE DHT22  // DHT 22

DHT dht(DHTPIN,DHTTYPE);

const char WEBSITE[] = "api.pushingbox.com"; //pushingbox API server
const String devid = "YOUR_DEVICEID"; //device ID on Pushingbox for our Scenario

const char* MY_SSID = "YOUR SSID";
const char* MY_PWD =  "YOUR WiFi PASSWORD";


int status = WL_IDLE_STATUS;
// if you don't want to use DNS (and reduce your sketch size)
// use the numeric IP instead of the name for the server:
//IPAddress server(74,125,232,128);  // numeric IP for Google (no DNS)


void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(9600);
  while (!Serial) 
  {
    ; // wait for serial port to connect. Needed for native USB port only
  }

  // check for the presence of the shield:
  if (WiFi.status() == WL_NO_SHIELD) {
    Serial.println("WiFi shield not present");
    // don't continue:
    while (true);
  }

  // attempt to connect to Wifi network:
  while (status != WL_CONNECTED) 
  {
    Serial.print("Attempting to connect to SSID: ");
    Serial.println(MY_SSID);
    //Connect to WPA/WPA2 network.Change this line if using open/WEP network
    status = WiFi.begin(MY_SSID, MY_PWD);

    // wait 10 seconds for connection:
    delay(10000);
  }
  
  Serial.println("Connected to wifi");
  printWifiStatus();
  
}

void loop() {

   // Wait between measurements.
  delay(10000);

  //prefer to use float, but package size or float conversion isnt working
  //will revise in future with a string fuction or float conversion function

  int humidityData = dht.readHumidity();
  // Read temperature as Celsius (the default)
  int celData = dht.readTemperature();
  // Read temperature as Fahrenheit (isFahrenheit = true)
  int fehrData = dht.readTemperature(true);

  // Check if any reads failed and exit early (to try again).
  if (isnan(humidityData) || isnan(celData) || isnan(fehrData))
  {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }

  // Compute heat index in Fahrenheit (the default)
  int hifData = dht.computeHeatIndex(fehrData, humidityData);
  // Compute heat index in Celsius (isFahreheit = false)
  int hicData = dht.computeHeatIndex(celData, humidityData, false);

  Serial.print("Humidity: ");
  Serial.print(humidityData);
  Serial.print(" %\t");
  Serial.print("Temperature: ");
  Serial.print(celData);
  Serial.print(" *C ");
  Serial.print(fehrData);
  Serial.print(" *F\t");
  Serial.print("Heat index: ");
  Serial.print(hicData);
  Serial.print(" *C ");
  Serial.print(hifData);
  Serial.println(" *F\n");

Serial.println("\nSending Data to Server..."); 
  // if you get a connection, report back via serial:
WiFiClient client;  //Instantiate WiFi object, can scope from here or Globally

    //API service using WiFi Client through PushingBox then relayed to Google
    if (client.connect(WEBSITE, 80))
      { 
         client.print("GET /pushingbox?devid=" + devid
       + "&humidityData=" + (String) humidityData
       + "&celData="      + (String) celData
       + "&fehrData="     + (String) fehrData
       + "&hicData="      + (String) hicData
       + "&hifData="      + (String) hifData
         );

      // HTTP 1.1 provides a persistent connection, allowing batched requests
      // or pipelined to an output buffer
      client.println(" HTTP/1.1"); 
      client.print("Host: ");
      client.println(WEBSITE);
      client.println("User-Agent: MKR1000/1.0");
      //for MKR1000, unlike esp8266, do not close connection
      client.println();
      Serial.println("\nData Sent"); 
      }
}


void printWifiStatus() {
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print your WiFi shield's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

Google Script for receiving wireless data in HTTPS format

JavaScript
Paste this into the Script Editor of your Google Sheet to receive your Wireless data. Remember it has to be encrypted/security certificate HTTPS format, that's why I'm using PushingBox
//-----------------------------------------------
//Originally published by Mogsdad@Stackoverflow
//Modified for jarkomdityaz.appspot.com
//Modified for Hackster.io by Stephen Borsay
//-----------------------------------------------
/*

GET request query:

https://script.google.com/macros/s/<gscript id>/exec?celData=data_here
----------------------------------------------------------------------

GScript, PushingBox and Arduino/ESP8266 Variables in order:

humidityDat
celData
fehrData
hicData
hifData
----------------------------------------------------
*/


/* Using spreadsheet API */

function doGet(e) { 
  Logger.log( JSON.stringify(e) );  // view parameters

  var result = 'Ok'; // assume success

  if (e.parameter == undefined) {
    result = 'No Parameters';
  }
  else {
    var id = '<YOUR_SPREADSHEET_URL_HERE';//docs.google.com/spreadsheetURL/d
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;
    var rowData = [];
    //var waktu = new Date();
    rowData[0] = new Date(); // Timestamp in column A
    
    for (var param in e.parameter) {
      Logger.log('In for loop, param='+param);
      var value = stripQuotes(e.parameter[param]);
      //Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'humidityData': //Parameter
          rowData[1] = value; //Value in column B
          break;
        case 'celData':
          rowData[2] = value;
          break;
        case 'fehrData':
          rowData[3] = value;
          break;
        case 'hicData':
          rowData[4] = value;
          break;
        case 'hifData':
          rowData[5] = value;
          break;
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));

    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }

  // Return result of operation
  return ContentService.createTextOutput(result);
}

/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

PushingBox URL String (for HTTPS glue)

HTML
This is your string to connect your Arduino/MKR1000 variable names being transmitted to Google Sheets with the exact same names. Using PushingBox as a security enabling intermediary RESTfully.
?humidityData=$humidityData$&celData=$celData$&fehrData=$fehrData$&hicData=$hicData$&hifData=$hifData$

Credits

Stephen Borsay

Stephen Borsay

11 projects • 73 followers
Computer Engineer: Embedded Systems and IoT. AWS IoT Hero www.udemy.com/user/stv/ device2cloud.net

Comments

Related channels and tags
  • Arduino
  • Google
  • Internet of Things
  • Internet of Things