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

README.md

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>

google_spreadsheet.coffee

class Dashing.GoogleSpreadsheet extends Dashing.Widget

google_spreadsheet.html

<h1 class="title" data-bind="title"></h1>
<h2 class="value" data-bind="value"></h2>
<p class="updated-at" data-bind="updatedAtMessage"></p>

google_spreadsheet.rb

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

google_spreadsheet.scss

// ----------------------------------------------------------------------------
// 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);
  }
}