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)

These are two of the most popular spreadsheet programs. Those who are comfortable using Google sheets swear by it, while Excel lovers prefer to stay within their Excel comfort zones. But if you need to convert excel to google sheets here is the step-by-step article

Thank you for an example, damn, there are so little examples of how to use google-apis-sheets_v4 ruby gem!
I'd like to add more examples – authentication through service account, and some basic operations:

service = Google::Apis::SheetsV4::SheetsService.new.tap do |s|
  s.authorization = Google::Auth::ServiceAccountCredentials.make_creds(
    json_key_io: File.open('config/google-service-secret.json'),
    scope: Google::Apis::SheetsV4::AUTH_SPREADSHEETS
  )
end

spreadsheet_id = 'ycsgI6WPeEsp3fYT7Ynulwmnxa50kHZGI3-w2ZwlPF0C'
sheet_name = 'Sheet1'

# get values
res = service.get_spreadsheet_values(spreadsheet_id, "#{sheet_name}!A2:C3")
res.values.each do |row|
  puts row.inspect
end

# update values
service.update_spreadsheet_value(spreadsheet_id, "#{sheet_name}!A2:A3", {
  values: [['BLA']]
}, value_input_option: 'RAW')

# batch update values
service.batch_update_values(spreadsheet_id, {
  data: [
    { range: "#{sheet_name}!A2:A3", values: [['BLA111']], value_input_option: 'RAW' },
    { range: "#{sheet_name}!A4:A5", values: [['BLA222']], value_input_option: 'RAW' },
  ],
  value_input_option: 'RAW',
})