Read data from a Google Spreadsheet and send it to Ubidots


#1

@George posted:

I am trying to use Google Scripts to get info out of a Google Sheets spreadsheet and send the info to Ubidots.

I got the example working with curl:

Georges-MacBook-Pro-3:~ george$ 
curl -XPOST -H 'Content-Type: application/json;' -H 'X-Auth-Token: YOv1ceM6ms0lAygETk5ObHELJSqrgi1FOes1xSLI' -d '{"value": 23}' http://things.ubidots.com/api/v1.6/variables/547881c2762542609d265fe4/values

{"url": "http://things.ubidots.com/api/v1.6/values/5478b26a7625423dabc0a9b6", "value": 23.0, "timestamp": 1417196138433, "context": {}, "created_at": "2014-11-28T17:35:38.433”}

I have not been able to figure out how to do the same from Google scripts or find an example. Any ideas?

This is the sample code from Google that I believe is applicable:

getRequest(url, params)

Returns the request that would be made if the operation were invoked. This method does not actually issue the request.

 // The code below logs the value for every key of the returned map.
 var fields = {'in_reply_to_screen_name': true, 'created_at': true, 'text': true};

 function tweet() {
   // Setup OAuthServiceConfig
   var oAuthConfig = UrlFetchApp.addOAuthService("twitter");
   oAuthConfig.setAccessTokenUrl("https://api.twitter.com/oauth/access_token");
   oAuthConfig.setRequestTokenUrl("https://api.twitter.com/oauth/request_token");
   oAuthConfig.setAuthorizationUrl("https://api.twitter.com/oauth/authorize");
   oAuthConfig.setConsumerKey(ScriptProperties.getProperty("twitterConsumerKey"));
   oAuthConfig.setConsumerSecret(ScriptProperties.getProperty("twitterConsumerSecret"));

   // Setup optional parameters to point request at OAuthConfigService.  The "twitter"
   // value matches the argument to "addOAuthService" above.
   var options =
     {
       "oAuthServiceName" : "twitter",
       "oAuthUseToken" : "always"
     };

   var result = UrlFetchApp.getRequest("https://api.twitter.com/1.1/statuses/user_timeline.json",
       options);
   for(i in result) {
     Logger.log(i + ": " + result[i]);
   }
 }

#2

The following example extracts data from a spreadsheet containing this data:

547513aa762542723907e9ff 23
547513aa762542723907e9ff 45

And sends the corresponding values in column 2 to the variables with IDs in column 1:

var TOKEN="AmUU5BccbQdKx4aXm4XZAIisb5Gddy";

/**
 * Post data to Ubidots using the Token
 * @param {variable} id of the variable for post data.
 * @param {payload} object with de data ex:{"value":23}
 * @return the Reponse
 */
function postUbidots(variable, payload){
  var options =
     {
       "contentType" : "application/json",
       "headers" : {"X-Auth-Token": TOKEN},
       "method": "post",
       "payload": JSON.stringify(payload)
     };

   var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/variables/"+variable+"/values",
       options);
   return response;
}



/**
 * Post data to Ubidots using the Token
 * @param {payload} collection for post data: http://ubidots.com/docs/api/v1_6/collections/post_values.html
 * @return the Reponse
 */
function postCollectionUbidots(payload){
  var options =
     {
       "contentType" : "application/json",
       "headers" : {"X-Auth-Token": TOKEN},
       "method": "post",
       "payload": JSON.stringify(payload)
     };

   var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/collections/values",
       options);
   return response;
}


/**
 * Retrieves all the rows in the active spreadsheet that contain data and logs the
 * values for each row.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
  var data = [];
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row.length == 2){
      data.push({"variable": row[0], "value": row[1]});
    }
    
  }
  postCollectionUbidots(data);
};


/**
 * Post data to UBIDOTS.
 *
 * @param {number} input The value to post.
 * @return The input multiplied by 2.
 * @customfunction
 */
function POST_UBIDOTS(input) {
  return input * 2;
}


function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Post Data', functionName: 'readRows'}
  ];
  spreadsheet.addMenu('Ubidots', menuItems);
}

The code creates a menu in the tool bar that says Ubidots.

We hope this is useful!


#3

@George wrote:

Thanks.

I tried a simple example using your function, but clearly I am still missing something. I am hoping it something stupid as I am new to Javascript :slight_smile:

This example works fine:

curl -XPOST -H ‘Content-Type: application/json;’ -H ‘X-Auth-Token: pKD17DNsSZ6QRCCS9pWIxxxx2pn3Wn’ -d ‘{“value”: 27}’ http://things.ubidots.com/api/v1.6/variables/547881c2762542609d265fe4/values
{“url”: “http://things.ubidots.com/api/v1.6/values/5486edcd7625427cae2f20d4”, “value”: 27.0, “timestamp”: 1418128845220, “context”: {}, “created_at”: "2014-12-09T12:40:45.220”}

This code example:

var TOKEN="pKD17DNsSZ6QRCCS9pWIC5Uo2pn3Wn";
var variable = "547881c2762542609d265fe4";
var payload = {value : 30.0};

/**
 * Post data to Ubidots using the Token
 * @param {variable} id of the variable for post data.
 * @param {payload} object with de data ex:{"value":23}
 * @return the Reponse
 */

function postUbidots(variable, payload){
  var options =
     {
       "contentType" : "application/json",
       "headers" : {"X-Auth-Token": TOKEN},
       "method": "post",
       "payload": JSON.stringify(payload),
       "muteHttpExceptions" : false
     };

   var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/variables/"+variable+"/values",
       options);
   return response;
}

I get this error:

Request failed for http://things.ubidots.com/api/v1.6/variables/undefined/values returned code 404. Truncated server response: {“detail”: “Not found”} (use muteHttpExceptions option to examine full response) (line 22, file “postUbidots”)

….George


#4

Hi George,

Sure, you’re getting the “Not Found” error because the API can’t find the resource you are sending, in this case the variable. This is because “variable” is defined in more than one place, so any of these solutions should work:

  1. Change the name of the global or local variable:

    var TOKEN=“pKD17DNsSZ6QRCCS9pWIC5Uo2pn3Wn”;
    var var = “547881c2762542609d265fe4”;



    var response = UrlFetchApp.fetch(“http://things.ubidots.com/api/v1.6/variables/"+var+"/values”, options);

  2. When calling the function, send the local variable:

    postUbidots(“547881c2762542609d265fe4”…)

  3. Change the name of the parameter that the function receives:

    function postUbidots(var, payload){


Any of these should work.


#5

Yeh I kinda mixed up my test code and production code …whoops :frowning:

So, to simplify I modified the function such that all the required info was contained in the function:

function postUbidots(variable, payload){
var TOKEN=“pKD17XXXXXXXRCCS9pWIC5Uo2pn3Wn”;
payload = 22;
variable = “547881cXXXXXXX2609d265fe4”;
var options =
{
“contentType” : “application/json”,
“headers” : {“X-Auth-Token”: TOKEN},
“method”: “post”,
“payload”: JSON.stringify(payload),
“muteHttpExceptions” : false
};
var response = UrlFetchApp.fetch(“http://things.ubidots.com/api/v1.6/variables/"+variable+"/values”,
options);
return response;
}

I then called the function from my spreadsheet, using dummy data:

=postUbidots(0,0)

I get error:

error: Request failed for http://things.ubidots.com/api/v1.6/variables/547881cXXXXXXXX2609d265fe4/values returned code 400. Truncated server response: {“non_field_errors”: [“Invalid data”]} (use muteHttpExceptions option to examine full response) (line 22, file “postUbidots”)

So it looks like it does not like my data. What am I missing in how the data is presented?


#6

So, the payload must be {“value”: 22}, example:

function postUbidots(variable, payload){
var TOKEN="pKD17XXXXXXXRCCS9pWIC5Uo2pn3Wn";
var payload = {"value": 22};
...
...
...

Regards,


#7

Thanks … I tried that with the same error:

function postUbidots(variable, payload){
var TOKEN=“pKD17DNsSZXXXXXXXXpWIC5Uo2pn3Wn”;
var payload = {“value”: 22};
var variable = “547881c2XXXXXXXX09d265fe4”;
var options =
{
“contentType” : “application/json”,
“headers” : {“X-Auth-Token”: TOKEN},
“method”: “post”,
“payload”: JSON.stringify(payload),
“muteHttpExceptions” : false
};
var response = UrlFetchApp.fetch(“http://things.ubidots.com/api/v1.6/variables/"+variable+"/values”,
options);
return response;
}

Returns:

error: Request failed for http://things.ubidots.com/api/v1.6/variables/547881c2XXXXXX9d265fe4/values returned code 400. Truncated server response: {“non_field_errors”: [“Invalid data”]} (use muteHttpExceptions option to examine full response) (line 13, file “postUbidots”)


#8

Not sure I understand why, but when I removed the return response(); statement, it starting working.

Ah… the fun of coding :slight_smile:


#9

Crewze Hi, I was checking but when I run the function I don’t have any error, however you can remove the return statement without any problem.

Here a portion of cleaner code that you can use, in idea is the same as having the conversation thread.

function postDataUbidots(){
  var TOKEN="XXXXX5Bcc8QdKx4eXm4XZAXXXXXXwdy";
  var payload = {"value": 22};
  var variable = "227513aa763542713947e9ff";
  var options =
      {
        "contentType" : "application/json",
        "headers" : {"X-Auth-Token": TOKEN},
        "method": "post",
        "payload": JSON.stringify(payload),
        "muteHttpExceptions" : false
      };
  var response = UrlFetchApp.fetch("http://things.ubidots.com/api/v1.6/variables/"+variable+"/values", options);
  return response; 
}


function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Post Data', functionName: 'postDataUbidots'}
  ];
  spreadsheet.addMenu('Ubidots', menuItems);
}

Regards,