Skip to content

Instantly share code, notes, and snippets.

@danielpowell4
Last active November 15, 2023 11:37
Show Gist options
  • Save danielpowell4/7f98ffe5efc01fd9cc473b6382c9a892 to your computer and use it in GitHub Desktop.
Save danielpowell4/7f98ffe5efc01fd9cc473b6382c9a892 to your computer and use it in GitHub Desktop.
Write reports straight to google sheets! We use this in our Rails app to run queries and share the data outside the app.
require 'google/apis/sheets_v4'
require 'google/apis/drive_v3'
require 'googleauth'
require 'fileutils'
class GoogleSheetReportWriter
def initialize(notification_email:)
@creds = build_credentials
@notification_email = notification_email
end
def to_csv(file_name, column_headers, results)
spreadsheet = create_spreadsheet(title: file_name)
write_sheet(
spreadsheet_id: spreadsheet.spreadsheet_id,
sheet_name: 'Sheet1',
column_headers: column_headers,
results: results
)
share_spreadsheet(spreadsheet)
puts "Link to spreadsheet: #{spreadsheet.spreadsheet_url}" # rubocop:disable Rails/Output
end
def to_spreadsheet(file_name, sheets)
# sheets is array of arrays: [title, headers, rows]
spreadsheet = create_spreadsheet(title: file_name)
add_sheets(spreadsheet_id: spreadsheet.spreadsheet_id, titles: sheets.map(&:first))
write_sheets(spreadsheet_id: spreadsheet.spreadsheet_id, sheets: sheets)
share_spreadsheet(spreadsheet)
puts "Link to spreadsheet: #{spreadsheet.spreadsheet_url}" # rubocop:disable Rails/Output
end
private
def auth_scopes
[
Google::Apis::SheetsV4::AUTH_SPREADSHEETS,
Google::Apis::DriveV3::AUTH_DRIVE
]
end
def build_credentials
creds = Google::Auth::ServiceAccountCredentials.make_creds(
scope: auth_scopes
)
creds.fetch_access_token!
creds
end
def create_spreadsheet(title:)
spreadsheet_request = Google::Apis::SheetsV4::Spreadsheet.new(
properties: {
title: title
}
)
sheets_service.create_spreadsheet(spreadsheet_request)
end
def write_sheet(spreadsheet_id:, sheet_name:, column_headers:, results:)
rows = [column_headers] + results
value_range = Google::Apis::SheetsV4::ValueRange.new(values: rows)
sheets_service.append_spreadsheet_value(
spreadsheet_id,
sheet_name,
value_range,
value_input_option: 'USER_ENTERED'
)
end
def add_sheets(spreadsheet_id:, titles:)
batch = { requests: [] }
titles.each do |title|
new_sheet_request = {
add_sheet: {
properties: {
title: title
}
}
}
batch[:requests] << new_sheet_request
end
# clears auto generated 'Sheet 1'
batch[:requests] << { delete_sheet: { spreadsheet_id: 0 } }
sheets_service.batch_update_spreadsheet(spreadsheet_id, batch, {})
end
def write_sheets(spreadsheet_id:, sheets:)
sheets.each do |title, column_headers, results|
write_sheet(
spreadsheet_id: spreadsheet_id,
sheet_name: title,
column_headers: column_headers,
results: results
)
end
end
def sheets_service
@sheets_service ||= build_sheets_service
end
def build_sheets_service
service = Google::Apis::SheetsV4::SheetsService.new
service.authorization = @creds
service
end
def share_spreadsheet(spreadsheet)
drive_service = Google::Apis::DriveV3::DriveService.new
drive_service.authorization = @creds
callback = lambda do |_res, err|
if err
raise err.body
end
end
user_permission = {
type: 'user',
role: 'writer',
email_address: @notification_email
}
drive_service.create_permission(spreadsheet.spreadsheet_id,
user_permission,
fields: 'id',
&callback)
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment