Skip to content

Instantly share code, notes, and snippets.

Last active Mar 12, 2020
What would you like to do?
!! Deprecated !! - Get and Show cell's value from Google Spreadsheet Widget for Dashing


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


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


dashing install e0ad37c2e137d2da0916


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

  1. Go to
  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 .


    • Specify the target Google Spreadsheet ID. If spreadsheet's url is , spreadsheet's id is abcdefghijklmnopqrstuvwxyz .
    • Specify worksheet number of the target Google Spreadsheet. This number is zero start.
    • Specify row number of target worksheet.
    • 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>
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
def authorize
client =
:application_name => 'Get Value from Google SpreadSheet for Dashing',
:application_version => '1.0.0')
file_storage ='credential-oauth2.json')
if file_storage.authorization.nil?
flow =
:client_id => ENV['GOOGLE_DRIVE_CLIENT_ID'],
:client_secret => ENV['GOOGLE_DRIVE_CLIENT_SECRET'],
:scope => %w(
client.authorization = flow.authorize(file_storage)
client.authorization = file_storage.authorization
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]
send_event('google_spreadsheet', { value: cell_value })
// ----------------------------------------------------------------------------
// 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);
Copy link

aazimh commented May 6, 2016


That did it, wow! Thank you so much!

Copy link

a-know commented May 7, 2016

👍 👍

Copy link

VinodDN commented Jun 15, 2016

Worked for me....

Copy link

dreamplay85 commented Nov 21, 2016

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?


Copy link

a-know commented Nov 28, 2016



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?

Copy link

kenold 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.
to update the authorized redirect URIs.

Copy link

a-know commented May 20, 2017

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

Copy link

NoriAbe commented Mar 11, 2020

I missed the OAuth page, so now my widget doesn't show anything. How can I re-enter the OAuth page. And any other reason why I can not see any number in my widget?

Copy link

a-know commented Mar 12, 2020

Hi. Thanks for your question.

You know, updating of Dashing itself has already stopped, and I am not using Dashing, so maintenance has not been done.
Therefore, I am sorry, but I cannot verify or investigate this question.
I'm sorry I couldn't help you.

This widget realized "displaying spreadsheet values ​​as a dashboard" is now based on Google Data Studio ( ). I think you can easily realize it with
Why don't you try it? :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment