Skip to content

Instantly share code, notes, and snippets.

@a-know a-know/README.md
Last active May 31, 2018

Embed
What would you like to do?
Get and Show cell's value from Google Spreadsheet Widget for Dashing

Description

Get and Show cell's value from Google Spreadsheet Widget for Dashing.

Dependency

  • gem 'google-api-client'
  • gem 'google_drive'

Installation

dashing install e0ad37c2e137d2da0916

Usage

To use this widget, you'll first need to set up a Google API project and get client_secret.json .

  1. Go to https://code.google.com/apis/console
  2. Click 'Create Project'
  3. Enable 'Drive API' service and accept both TOS's
  4. Click 'API Access' in the left-hand nav menu
  5. Click 'Create an OAuth 2.0 Client ID'
  6. Enter a product name (e.g. Dashing Widget) - logo and url are optional
  7. Click 'Next'
  8. Under Application Type, select 'Installed Application'
  9. Click 'Create Client ID'
  10. Click 'Download JSON'
  11. Open JSON file and note client_id and client_secret .
  12. Set client_id and client_secret as ENV['GOOGLE_DRIVE_CLIENT_ID'] and ENV['GOOGLE_DRIVE_CLIENT_SECRET'] .
  13. Install this widget and open dashboard with web-browser. Then, redirect to Google Drive OAuth Page. If you heck the permissions, approve the request. (credential-oauth2.json is created.)
  14. Second time later without being asked for approval. Start use by setting Configuration below .

Configuration

  • ENV['DASHING_TARGET_SPREAD_SHEET_ID']
    • Specify the target Google Spreadsheet ID. If spreadsheet's url is https://docs.google.com/spreadsheets/d/abcdefghijklmnopqrstuvwxyz/ , spreadsheet's id is abcdefghijklmnopqrstuvwxyz .
  • WORKSHEET_NUMBER
    • Specify worksheet number of the target Google Spreadsheet. This number is zero start.
  • CELLS_ROW_NUMBER
    • Specify row number of target worksheet.
  • CELLS_COLUMN_NUMBER
    • Specify column number of target worksheet.

Finally, add this to your dashboard layout file.

<li data-row="1" data-col="1" data-sizex="1" data-sizey="1">
  <div data-id="google_spreadsheet" data-view="GoogleSpreadsheet" data-title="Google Spreadsheet Cells Value"></div>
</li>
class Dashing.GoogleSpreadsheet extends Dashing.Widget
<h1 class="title" data-bind="title"></h1>
<h2 class="value" data-bind="value"></h2>
<p class="updated-at" data-bind="updatedAtMessage"></p>
require 'google/api_client'
require 'google/api_client/client_secrets'
require 'google/api_client/auth/file_storage'
require 'google/api_client/auth/installed_app'
require 'google_drive'
WORKSHEET_NUMBER = 1 # zero start
CELLS_ROW_NUMBER = 1
CELLS_COLUMN_NUMBER = 1
def authorize
client = Google::APIClient.new(
:application_name => 'Get Value from Google SpreadSheet for Dashing',
:application_version => '1.0.0')
file_storage = Google::APIClient::FileStorage.new('credential-oauth2.json')
if file_storage.authorization.nil?
flow = Google::APIClient::InstalledAppFlow.new(
:client_id => ENV['GOOGLE_DRIVE_CLIENT_ID'],
:client_secret => ENV['GOOGLE_DRIVE_CLIENT_SECRET'],
:scope => %w(
https://www.googleapis.com/auth/drive
https://docs.google.com/feeds/
https://docs.googleusercontent.com/
https://spreadsheets.google.com/feeds/
),
)
client.authorization = flow.authorize(file_storage)
else
client.authorization = file_storage.authorization
end
client
end
SCHEDULER.every '10m', :first_in => 0 do |job|
client = authorize
session = GoogleDrive.login_with_oauth(client.authorization.access_token)
ws = session.spreadsheet_by_key(ENV['DASHING_TARGET_SPREAD_SHEET_ID']).worksheets[WORKSHEET_NUMBER]
cell_value = ws[CELLS_ROW_NUMBER, CELLS_COLUMN_NUMBER]
send_event('google_spreadsheet', { value: cell_value })
end
// ----------------------------------------------------------------------------
// Sass declarations
// ----------------------------------------------------------------------------
$background-color: #9b59b6;
$value-color: #fff;
$title-color: rgba(255, 255, 255, 0.7);
// ----------------------------------------------------------------------------
// Widget-google-spreadsheet styles
// ----------------------------------------------------------------------------
.widget-google-spreadsheet {
background-color: $background-color;
.title {
color: $title-color;
font-size: 30px;
}
.value {
color: $value-color;
}
.updated-at {
color: rgba(0, 0, 0, 0.3);
}
}
@BritneyMuller

This comment has been minimized.

Copy link

commented Dec 7, 2015

Where exactly do the Configuraiton steps take place? In the .rb file? Can't get this to work.... thank you!

@minisaurus

This comment has been minimized.

Copy link

commented Jan 14, 2016

I don't understand what "Then, redirect to Google Drive OAuth Page." means. What am I doing there?

@Roshanjossey

This comment has been minimized.

Copy link

commented Feb 26, 2016

@BritenyMuller: If you're referring to the things under Configuration title,

DASHING_TARGET_SPREAD_SHEET_ID should be set as your environment variable, like
$ export DASHING_TARGET_SPREAD_SHEET_ID=abcdefghijklmnopqrstuvwxyz
in your shell or put it in your shell profile (eg: .bash_profile)

Rest of the configuration can be found in google_spreadsheet.rb itself

@sjanett

This comment has been minimized.

Copy link

commented Mar 1, 2016

Can someone please provide more information on how to get this up and running. I have setup as per the instructions. Created and entered in all the API's but all I get is a widget with nothing in it.
Elaborating on "Then, redirect to Google Drive OAuth Page." would help!
Thanks

@a-know

This comment has been minimized.

Copy link
Owner Author

commented Mar 3, 2016

@sjanett
If you can be installed correctly this widget, you should be automatically redirected to the OAuth authentication screen after opening the dashing dashboard in a web browser.
Please tell me the contents of the browser window or application logs.

@aazimh

This comment has been minimized.

Copy link

commented May 1, 2016

@a-know

I keep having to re-download the credentials.json file, it only works once and then stops updating. Where exactly does it need to be placed?

Thanks.

@a-know

This comment has been minimized.

Copy link
Owner Author

commented May 2, 2016

@aazimh

hmm..., you said updating is view refreshing by dashing?
or, for example, dashing application restart?

@aazimh

This comment has been minimized.

Copy link

commented May 6, 2016

@a-know

It's the normal updating through the scheduler. Here's the console output:


scheduler caught exception:
Invalid time value 1462494735
/var/lib/gems/1.9.1/gems/signet-0.7.2/lib/signet/oauth_2/client.rb:1183:in `normalize_timestamp'
/var/lib/gems/1.9.1/gems/signet-0.7.2/lib/signet/oauth_2/client.rb:759:in `issued_at='
/var/lib/gems/1.9.1/gems/signet-0.7.2/lib/signet/oauth_2/client.rb:234:in `update_token!'
/var/lib/gems/1.9.1/gems/signet-0.7.2/lib/signet/oauth_2/client.rb:192:in `update!'
/var/lib/gems/1.9.1/gems/signet-0.7.2/lib/signet/oauth_2/client.rb:107:in `initialize'
/var/lib/gems/1.9.1/gems/google-api-client-0.7.1/lib/google/api_client/auth/file_storage.rb:48:in `new'
/var/lib/gems/1.9.1/gems/google-api-client-0.7.1/lib/google/api_client/auth/file_storage.rb:48:in `block in load_credentials'
/var/lib/gems/1.9.1/gems/google-api-client-0.7.1/lib/google/api_client/auth/file_storage.rb:46:in `open'
/var/lib/gems/1.9.1/gems/google-api-client-0.7.1/lib/google/api_client/auth/file_storage.rb:46:in `load_credentials'
/var/lib/gems/1.9.1/gems/google-api-client-0.7.1/lib/google/api_client/auth/file_storage.rb:39:in `initialize'

Any help would be appreciated!

@a-know

This comment has been minimized.

Copy link
Owner Author

commented May 6, 2016

@aazimh
Thank you showing your console output!

I don't know the cause yet, can you specify and use signet gem version 0.5.1 or 0.6.1 ?
It seems like to changed issued_at timestamp handling in signet-0.7.2 you using.

@aazimh

This comment has been minimized.

Copy link

commented May 6, 2016

@a-know

That did it, wow! Thank you so much!

@a-know

This comment has been minimized.

Copy link
Owner Author

commented May 7, 2016

👍 👍

@VinodDN

This comment has been minimized.

Copy link

commented Jun 15, 2016

Worked for me....

@dreamplay85

This comment has been minimized.

Copy link

commented Nov 21, 2016

Hello
It works for me

I have a spreadsheet and i would like to show only the today date ROW

Is-it possible and how please?

Thanks!

@a-know

This comment has been minimized.

Copy link
Owner Author

commented Nov 28, 2016

@dreamplay85

Hello!

I have a spreadsheet and i would like to show only the today date ROW

Well, if you modify this script, it seems to be possible, but it seems to be better to adjust by the spreadsheet.
For example, How about using the VLOOKUP function to set the number corresponding to today's line in a certain cell?

@kenold

This comment has been minimized.

Copy link

commented Apr 4, 2017

@a-know This isn't working for me. When I start dashboard, I got

Error: redirect_uri_mismatch

The redirect URI in the request, http://localhost:9292/, does
not match the ones authorized for the OAuth client.
Visit https://console.developers.google.com/apis/credentials/oauthclient/xxxxx5861-gi031vqvit2drtai0msk0bgg9i29ou1s.apps.googleusercontent.com?project=661296021234
to update the authorized redirect URIs.

@a-know

This comment has been minimized.

Copy link
Owner Author

commented May 20, 2017

@kenold
Excuse my late reply. 'OAuth 2.0 Client ID' may be called a service account now. Please check the IAM Management console.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.