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.

13 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
    Replies
    1. Thanks! I'll have a look at that add on.

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

    Cheers!

    ReplyDelete
  3. Along these lines, at the present work rates you could without much of a stretch pay three or $400. That is certainly a remark about. free-lookup.net

    ReplyDelete
  4. Copyright it before you endeavor to offer a content with Writers Guild of America. This will guarantee that the content or its thought won't be stolen. script doctor

    ReplyDelete
  5. The touch screen is under three creeps in estimate! The touch screen isn't even three inches large!The Motorola Quench is one of the best Android phones. adlist24.com

    ReplyDelete
  6. The problem can range all over the place, it could be a problem with your printer, computer or who knows maybe even your cell phone.get free ssl certificate

    ReplyDelete
  7. For some years now. People of all walks of life mostly people at home have been hit by Microsoft free business calls Scams claiming that they work for Microsoft whilst mentioning statements such as "Your license key code is incorrect" or "Your Windows Computer needs to be updated". Whilst these statements may be a reality.

    ReplyDelete
  8. This is very interesting content! I have thoroughly enjoyed reading your points and have come to the conclusion that you are right about many of them. You are great. gbwhatsapp ios

    ReplyDelete
  9. The very popular activities of downloading music, pictures, and video are indeed available in the US cellular market fm mobile

    ReplyDelete
  10. On the off chance that you maintain an armada of business phones, you should refresh them to smartphones as quickly as time permits. Your staff will love you for it and you will see profits by it. You will love it!comment localiser une personne

    ReplyDelete
  11. We have sell some products of different custom boxes.it is very useful and very low price please visits this site thanks and please share this post with your friends. Mobilemaya

    ReplyDelete