Editing Google Sheets with the Google::Apis::SheetsV4 Ruby Client

test_google_sheets_apiV4.rb

#! /usr/bin/ruby

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 Test'
CLIENT_SECRETS_PATH = 'client_secret.json'
CREDENTIALS_PATH = File.join(Dir.pwd, '.credentials',
                             "sheets.googleapis.com-test.yaml")
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

##
# 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


spreadsheet_id = 'xyz123'

# Initialize the API
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

# Add new sheet to a spreadsheet

sheet_name = '2020'
column_count = 55

add_sheet_request = Google::Apis::SheetsV4::AddSheetRequest.new
add_sheet_request.properties = Google::Apis::SheetsV4::SheetProperties.new
grid_properties = Google::Apis::SheetsV4::GridProperties.new

add_sheet_request.properties.title = sheet_name 
grid_properties.column_count = column_count
add_sheet_request.properties.grid_properties = grid_properties

batch_update_spreadsheet_request = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
batch_update_spreadsheet_request.requests = Google::Apis::SheetsV4::Request.new

batch_update_spreadsheet_request_object = [ add_sheet: add_sheet_request ] 
batch_update_spreadsheet_request.requests = batch_update_spreadsheet_request_object 

response = service.batch_update_spreadsheet(spreadsheet_id, batch_update_spreadsheet_request)

puts ">>>>>>>>>> response: #{response.inspect}"

# Update values to a spreadsheet

range = 'Sheet1!A1:C2'

value_range_object = {
  "major_dimension": "ROWS",
  "values": [
     ["Multiplicand", "Multiplier", "Result"],
     ["2", "8", "=A2*B2"]
   ]
}

response = service.clear_values(spreadsheet_id, "Sheet1!A1:Z99")

response = service.update_spreadsheet_value(spreadsheet_id, range, value_range_object, value_input_option: 'RAW') # or USER_ENTERED

# Get spreadsheet properties

response = service.get_spreadsheet(spreadsheet_id)

puts ">>>>>>>>>> response: #{response.inspect}"

response.sheets.each do |s|
  puts s.properties.index 
  puts s.properties.title
  puts s.properties.grid_properties.column_count
  puts s.properties.sheet_id
end

# Append new columns to a spreadsheet

append_dimension_request = Google::Apis::SheetsV4::AppendDimensionRequest.new

append_dimension_request.dimension = 'COLUMNS'
append_dimension_request.length = 30
append_dimension_request.sheet_id = 1491311133

batch_update_spreadsheet_request = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
batch_update_spreadsheet_request.requests = Google::Apis::SheetsV4::Request.new

batch_update_spreadsheet_request_object = [ append_dimension: append_dimension_request ]
batch_update_spreadsheet_request.requests = batch_update_spreadsheet_request_object

response = service.batch_update_spreadsheet(spreadsheet_id, batch_update_spreadsheet_request)