CODEWITHSUNDEEP
rubygoogle-sheets-api
markhorrocks
markhorrocks

Reputation: 1518

How to create a new worksheet with Google Sheets API Ruby Client?

I'm using the ruby client to update an existing spreadsheet range with the class Google::Apis::SheetsV4::SheetsService but can't find a way to create a new sheet. I want to create a new sheet for each new year. I want to test for the existence of a sheet within the spreadsheet by title and add a new sheet if it doesn't exist. I can't find any examples of ruby code which helps me to accomplish my task.

This can not be a duplicate of the link proposed as a duplicate question as the ruby code in my answer below is very different to the solution written in C#.

http://www.rubydoc.info/github/google/google-api-ruby-client/Google/Apis/SheetsV4/SheetsService

Here is some of my code:

require 'google/apis/sheets_v4'

SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS

spreadsheet_id = '1NTvP-VkDDE1_xzz_etc'

Upvotes: 3

Views: 2751

Answers (1)

markhorrocks
markhorrocks

Reputation: 1518

Ok, here is the answer which I consider is very much different code from the suggested duplicate and which reference to should be removed. This code includes methods for querying sheet properties and updating values and appending new columns to a sheet.

This answer should be read in conjunction with https://developers.google.com/drive/v3/web/quickstart/ruby

Common code:

service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize

Adding a new sheet:

sheet_name = '2020'
column_count = 55

add_sheet_request = Google::Apis::SheetsV4::AddSheetRequest.new
add_sheet_request.properties = Google::Apis::SheetsV4::SheetProperties.new
add_sheet_request.properties.title = sheet_name 

grid_properties = Google::Apis::SheetsV4::GridProperties.new
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}"

Updating spreadsheet values:

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: 'USER_ENTERED')

Getting a spreadsheet's properties, title, and column count:

response = service.get_spreadsheet(spreadsheet_id)
puts ">>>>>>>>>> response: #{response.inspect}"

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

Appending new columns to a sheet:

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)

Upvotes: 12

Related Questions