TLDR: Using Sheet API

Set these 4 ENV source

export GOOGLE_ACCOUNT_TYPE=service_account
export GOOGLE_CLIENT_ID=000000000000000000000
export [email protected]
export GOOGLE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\\\\n...\\\\n-----END PRIVATE KEY-----\\\\n"

Add Google Sheet gem to Gemfile

gem 'google-apis-sheets_v4'

Sample code to create new sheet

service = Google::Apis::SheetsV4::SheetsService.new
service.authorization = ::Google::Auth::ServiceAccountCredentials.make_creds(scope: Google::Apis::SheetsV4::AUTH_SPREADSHEETS)

spreadsheet = {
  properties: {
	title: 'Sales Report'
  }
}
spreadsheet = service.create_spreadsheet(spreadsheet, fields: 'spreadsheetId')
puts "Spreadsheet ID: #{spreadsheet.spreadsheet_id}"

There is an issue with Sheet API: the sheet is always created in the root folder of the Drive. The documentation suggests 2 solutions, both involves Drive API, so it's probably better to use Drive API to create a new sheet.

TLDR: Using Drive API

Set these 4 ENV source

export GOOGLE_ACCOUNT_TYPE=service_account
export GOOGLE_CLIENT_ID=000000000000000000000
export [email protected]
export GOOGLE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\\\\n...\\\\n-----END PRIVATE KEY-----\\\\n"

Add Google Drive gem to Gemfile

gem 'google-apis-drive_v4'

Sample code to clone a sheet and put it inside a specific folder

drive = Google::Apis::DriveV3::DriveService.new
drive.authorization = ::Google::Auth::ServiceAccountCredentials.make_creds(scope: Google::Apis::SheetsV4::AUTH_DRIVE)

# from_id: ID of the spreadsheet file to copy from
# drive_id: ID of the drive in use
# folder_id: ID of the folder where the new file is located
drive.copy_file(
  from_id,
  {
	name:,
	parents: [folder_id],
	drive_id: drive_id,
	mime_type: 'application/vnd.google-apps.spreadsheet'
  },
  supports_all_drives: true
)

Complaints & Explanation

Working with Google API is always unpleasant: the API documentation itself sucks and spread all over different places depending on what services you need to integrate, the examples are often out of date, and the setup progress just for authorization is crazy.

I have a task to create a Google Sheet via API using service account.

The document is here https://developers.google.com/sheets/api/guides/create and the Ruby example links to this file https://github.com/googleworkspace/ruby-samples/blob/main/sheets/snippets/lib/spreadsheet_snippets.rb