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

Question

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'

Did you even look through documentation? This is very prominent in the Ruby client documentation: http://www.rubydoc.info/github/google/google-api-ruby-client/Google/Apis/SheetsV4/AddSheetRequest There are also many other questions on Stack Overflow that relate to adding a sheet via the REST API (though they are not necessarily specific to the `Ruby` language, they all use the same REST API and have the same methods available). — tehhowch, Apr 15 '18 at 19:12
Possible duplicate of [How to add a sheet in google sheets API v4 in C#?](https://stackoverflow.com/questions/37623191/how-to-add-a-sheet-in-google-sheets-api-v4-in-c) — tehhowch, Apr 15 '18 at 19:13
You don't need to be using `c#` to read and understand how they create a sheet with their Google-made client library and then rewrite it with your own Google-made client library. — tehhowch, Apr 16 '18 at 13:15
I can't find a code example of how to implement this and apparently too stupid to figure out how from the documentation linked above, that's why I asked here. The C# example bears no resemblance to my code so isn't much help. — markhorrocks, Apr 16 '18 at 14:58
Please edit your question statement to reflect that there is a method to create a new worksheet inside of a spreadsheet (your question states no such method exists). You should also include how you have attempted to use this method that exists and how it has failed. — tehhowch, Apr 16 '18 at 15:06
Please review your comment that this is a duplicate of the C# solution as you suggested. I believe that the code in my answer is sufficiently different due to being written in a different language that it does not constitute a duplicate question. — markhorrocks, Apr 17 '18 at 20:07
@tehhowch please add an answer referring to the C# code so I can award you the points. While the C# answer is not a complete answer to this question I did draw some inspiration from it as to how to accomplish the task — markhorrocks, Apr 23 '18 at 15:46

markhorrocks · Accepted Answer · 2018-04-17T22:45:38.800

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)

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

1 Answers1