Thursday, February 12, 2015

Use Google Sheets cell content in script


Wouldn't it be nice to collect data using a Google Sheets Form and be able to get each cell value from a script on your phone or computer? It can be done.

I'm assuming the data you need for your script is already in a Google Sheet. It could be collected using a form or similar. To get unauthenticated access to a Google Sheet it needs to be published. Either publish the sheet containing the data or create a and publish new Google Sheet to hold only the data needed for the script. Import the data to that new sheet using =IMPORTRANGE().

To get the URL for the published sheet, have a look at this page: https://developers.google.com/gdata/samples/spreadsheet_sample. Basically you just need the key for the sheet.

The published sheet URL looks something like this:

Cell C2 would look like this:

OK, on to the content. Below is the content I get when requesting cell R1C1. The actual data is between the tags <content type='text'> and </content>

<?xml version='1.0' encoding='UTF-8'?><entry xmlns='http://www.w3.org/2005/Atom' xmlns:batch='http://schemas.google.com/gdata/batch' xmlns:gs='http://schemas.google.com/spreadsheets/2006'><id>https://spreadsheets.google.com/feeds/cells/<key>/od6/public/basic/R1C1</id><updated>2014-12-12T07:35:19.026Z</updated><category scheme='http://schemas.google.com/spreadsheets/2006' term='http://schemas.google.com/spreadsheets/2006#cell'/><title type='text'>A1</title><content type='text'>2</content><link rel='self' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/<key>/od6/public/basic/R1C1'/></entry>

We just need to split the content on <content type='text'> and then split the second part again on </content>. The cell data is in the first part of the second split.

PowerShell example:
$CellA1 = Invoke-WebRequest -UseBasicParsing -Uri https://spreadsheets.google.com/feeds/cells/1DzK0hnq9bexu6h1ryZzPP6-WWk55IEx0xZrtFKK6UZI/od6/public/basic/R1C1
$Value = (($CellA1 -split "<content type=.text.>") -split "</content")[1]
Tasker example:
GetValueFromSheet (4)
A1: HTTP Get [ Server:Port:https://spreadsheets.google.com/ Path:feeds/cells/<key>/od6/public/basic/R1C1 Attributes: Cookies: User Agent: Timeout:10 Mime Type: Output File: Trust Any Certificate:Off ] 
A2: Variable Split [ Name:%HTTPD Splitter:<content type='text'> Delete Base:On ] 
A3: Variable Split [ Name:%HTTPD2 Splitter:</content> Delete Base:On ] 
A4: Variable Set [ Name:%Value To:%HTTPD21 Do Maths:Off Append:Off ] 
The content of A1 in the Google Sheet is now in Tasker variable %Value. You could use this to trigger other Tasker tasks or to present that value on a Zooper Widget, or send it to Pushover. I use it for a few things, and might write a more detailed end-to-end post on that.

3 comments:

  1. Hii Peter I am using Trigger and Send google sheet add on for the similar task. This add on will enable webhook in google sheet that will notify whenever any change made in the sheet and also move data from google sheet to any API. You can find it here at : https://chrome.google.com/webstore/detail/trigger-send/lmpdaoninbfpblmajgcodhookngnekek

    ReplyDelete
  2. Thank you for sharing this information Peter.
    It helped me tremendously today.

    Cheers!

    ReplyDelete