Getting multiple ranges from multiple worksheets from one spreadsheet using SheetsV4 with Ruby

[Bill Noseworthy]

I'm really just stumbling my way through this stuff. I haven't programmed in quite a while (mostly Java) and Ruby is very new to me.

OK...so I have a GoogleSheets spreadsheet on GD that I am trying to pull statistics from to display on a Dashing dashboard. I can get a single result NP but when I try and get a second range of values, I get a range parsing error.

I've scoured the web and API docs but I'm not sure of what to look for. I've tried numerous different ways to structure range but nothing works.

Any help would be appreciated.

Thanks.

Bill

require 'google/apis/sheets_v4'

require 'googleauth'

require 'googleauth/stores/file_token_store'

require 'fileutils'

OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'

APPLICATION_NAME = 'Google Sheets API Ruby Quickstart'

CLIENT_SECRETS_PATH = 'client_secret.json'

CREDENTIALS_PATH = File.join(Dir.home, '.credentials',"sheets.googleapis.com-ruby-quickstart.yaml")

SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY

SCHEDULER.every '1m', :first_in => 0 do |job|

##

# Ensure valid credentials, either by restoring from the saved credentials

# files or intitiating an OAuth2 authorization. If authorization is required,

# the user's default browser will be launched to approve the request.

#

# @return [Google::Auth::UserRefreshCredentials] OAuth2 credentials

def authorize

 FileUtils.mkdir_p(File.dirname(CREDENTIALS_PATH))

 client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)

 token_store = Google::Auth::Stores::FileTokenStore.new(file: CREDENTIALS_PATH)

 authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)

 user_id = 'default'

 credentials = authorizer.get_credentials(user_id)

 if credentials.nil?

   url = authorizer.get_authorization_url(

     base_url: OOB_URI)

   puts "Open the following URL in the browser and enter the " +

        "resulting code after authorization"

   puts url

   code = gets

   credentials = authorizer.get_and_store_credentials_from_code(

     user_id: user_id, code: code, base_url: OOB_URI)

 end

 credentials

end

# Initialize the API

service = Google::Apis::SheetsV4::SheetsService.new

service.client_options.application_name = APPLICATION_NAME

service.authorization = authorize

# InfoRad Sheet - https://docs.google.com/spreadsheets/d/1-zBn6QU3SyG...r65ycB1eUTrcc/edit

spreadsheet_id = '1-zBn6QU3SyG...r65ycB1eUTrcc' # InfoRad

range = 'Units Deployed!D3'&'Cost vs Profit!B6:D6'

response = service.get_spreadsheet_values(spreadsheet_id, range)

response.values.each do |row|

# Send InfoRad data to the GoogleSpreadsheet

puts row[0].to_str

puts row[1].to_str

send_event( 'activeUnits', { current: "#{row[0]}" } )
                send_event( 'costVsProfit', { current: "#{row[1]}" } )

  end

end