Write reports straight to google sheets! We use this in our Rails app to run queries and share the data outside the app.

google_sheet_report_writer.rb

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