Search code examples
rubygoogle-sheetsgoogle-sheets-apiexport-to-excel

Fetching a single sheet from a Google Spreadsheet as bytes in Ruby

I'm trying to retrieve a single sheet from a google spreadsheet in excel format, I have all the access setup correctly and can run different google sheet v4 api functions on it.

I wanted to use the Google::Apis::SheetsV4::SheetsService::copy_spreadsheet function to copy a single sheet as mentioned in the Ruby example here - https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.sheets/copyTo

This is my code -

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

spreadsheet_id = "<passing my spreadsheet id here>"
gid = "<setting this as my sheet id from the spreadsheet>"
request = Google::Apis::SheetsV4::CopySheetToAnotherSpreadsheetRequest.new(
        destination_spreadsheet_id: "0",
      )
response1 = service.copy_spreadsheet(spreadsheet_id,gid,request)

puts response1.to_json

This always fails with the following error -

/usr/local/lib/ruby/gems/3.1.0/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:229:in `check_status': badRequest: Invalid destinationSpreadsheetId [0] (Google::Apis::ClientError)
    from /usr/local/lib/ruby/gems/3.1.0/gems/google-apis-core-0.4.2/lib/google/apis/core/api_command.rb:134:in `check_status'

Would be great if someone can help me on how to use this properly, also if there's a better way to download/export a single sheet from a spreadsheet in Ruby let me know.

Solution

  • Answer for question 1

    This always fails with the following error -

      /usr/local/lib/ruby/gems/3.1.0/gems/google-apis-core-0.4.2/lib/google/apis/core/http_command.rb:229:in `check_status': badRequest: Invalid destinationSpreadsheetId [0] (Google::Apis::ClientError)
  from /usr/local/lib/ruby/gems/3.1.0/gems/google-apis-core-0.4.2/lib/google/apis/core/api_command.rb:134:in `check_status'

    Would be great if someone can help me on how to use this properly,

    When I saw your error message and your script, I thought that destination_spreadsheet_id: "0", is not correct. In this case, please set the destination Spreadsheet ID. When this is reflected in your script, it becomes as follows.

    src_spreadsheet_id = "###" # Please set the source Spreadsheet ID.
src_sheet_id = "###" # Please set the sheet ID of the source Spreadsheet.
dst_spreadsheet_id = "###" # Please set the destination Spreadsheet ID.

request = Google::Apis::SheetsV4::CopySheetToAnotherSpreadsheetRequest.new(
  destination_spreadsheet_id: dst_spreadsheet_id,
)
response1 = service.copy_spreadsheet(src_spreadsheet_id,src_sheet_id,request)
puts response1.to_json

    Answer for question 2

    I'm trying to retrieve a single sheet from a google spreadsheet in excel format, I have all the access setup correctly and can run different google sheet v4 api functions on it.

    also if there's a better way to download/export a single sheet from a spreadsheet in Ruby let me know.

    In this case, how about the following sample script? In this script, a XLSX data including the specific sheet is downloaded using the endpoint of https://docs.google.com/spreadsheets/d/{spreadsheetId}/export?format=xlsx&gid={sheetId}. So, please set your Spreadsheet ID and sheet ID to the URL. And, in this case, the access token is retrieved from service you are using.

    url = 'https://docs.google.com/spreadsheets/d/{spreadsheetId}/export?format=xlsx&gid={sheetId}'
filename = 'sample.xlsx' # Please set the saved filename.

access_token = service.request_options.authorization.access_token
open(
  url,
  "Authorization" => "Bearer " + access_token,
  :redirect => true
) do |file|
  open(filename, "w+b") do |out|
    out.write(file.read)
  end
end
    • When this script is run, the specific sheet of sheetId of spreadsheetId is downloaded as a XLSX data and save it.
    • This script uses require "open-uri".

    Note:

    • When the Spreadsheet is downloaded as a XLSX data, when an error related to the scope, please add https://www.googleapis.com/auth/drive.readonly to the scopes and reauthorize again. By this, the script works.

    Reference: