I tried to create an API to get data from a spreadsheet in Ruby (with service account)

Overview

I tried to create an API that gets data from a spreadsheet and formats it into json. Surprisingly, there were few articles using service accounts, so I hope it will be helpful as much as possible.

environment

Ruby: 2.5.7 Ruby on Jets: 2.3.18 (Since it is a Rails-like framework, you can do it with Rails)

About service account

An account used at the application level, not the user. When authenticating as a user, it is inconvenient because the authentication information must be changed when the user is out of the management of the application.

Since the service account does not depend on the user, this is convenient when hitting the API from the application regardless of the user.

By the way, this time I used a service account because I use it to hit the API from the back end regardless of the user.

Create a service account

If you have not created it, please create it referring to the following. I will omit it here

https://support.google.com/a/answer/7378726?hl=ja

Get credential file as json file from service account

Credentials> Manage service accounts> Operate created accounts> Create keys

Create a key in json format, After downloading, save it as google-sa-credential.json in the top directory of the app

Enable API

https://console.developers.google.com/apis/dashboard

From "APIs and Services" on the GCP console

• Google Drive API
• Google Sheets API

Search for and enable each.

Create folders and spreadsheets with your service account

When working with spreadsheets with a service account, you need to do one of the following:

--Created by a service account --Service account is shared as an editor

• It has been published

(Gem google drive description)

If you can allow the service account in "Share" of the spreadsheet, you don't need this task at the time of sharing, so you can skip it.

I have described two rake tasks below

--Create a folder under the specified folder ID (create a folder on the top floor if not specified) --Create a spreadsheet under the specified folder ID

# lib/tasks/google_drive.rake

namespace :google_drive do
  desc 'Create folder with Service account'
  task :create_folder, [:title, :email_address, :collection_id] => :environment do |_, args|
    session = create_session
    folder = if args[:collection_id]
               session.file_by_id(args[:collection_id]).create_subcollection(args[:title])
             else
               session.root_collection.create_subcollection(args[:title])
             end

    folder.acl.push(type: 'user', email_address: args[:email_address], role: 'writer')
    p "Created folder: #{folder.human_url}"
  end
  
  desc 'Create sheet with Service account'
  task :create_sheet, [:title, :email_address, :collection_id] => :environment do |_, args|    
    session = create_session
    sheet = session.file_by_id(args[:collection_id]).create_spreadsheet(args[:title])
    sheet.acl.push(type: 'user', email_address: args[:email_address], role: 'writer')
    p "Created sheet: #{sheet.human_url}"
  end
  
  def create_session
    ::GoogleDrive::Session.from_service_account_key('google-sa-credential.json')
  end
end

$ bundle exec rake 'google_drive:create_folder[<Folder title>,<Email address of the user you want to share>]'
$ bundle exec rake 'google_drive:create_sheet[<Sheet title>,<Email address of the user you want to share>]'

Supplement

collection: Meaning of folder

ACL: Access control list. Something similar to IAM and not. It seems to give permissions to buckets and objects flexibly.

Subsequent implementation policy

1. Use gem google_drive
   • spreadsheet → worksheet → json
2. Use gem google_api_client --Inflexible (even if you can output with json, you have to convert it to the shape you want anyway)

I implemented it with 1 policy so that the json structure can be changed flexibly.

It is divided into modules in some places, but it is not essential Also, omit routing

Gemfile

gem 'google_drive'

controller

# controller

session = GoogleDriveSession.create_session

service = SpreadSheetToHashService.new(session)
service.run!

render json: JSON.dump(service.records)

session creation module

# google_drive_session.rb

module GoogleDriveSession
  CREDENTIAL_PATH = 'google-sa-credential.json'

  def self.create_session
    return ::GoogleDrive::Session.from_service_account_key(CREDENTIAL_PATH)
  end
end

By the way, in my case, I get it from Secrets Manager except in the development environment (use gem'aws-sdk-secrets manager')

# google_drive_session.rb

module GoogleDriveSession
  CREDENTIAL_PATH = 'google-sa-credential.json'

  def self.create_session
    return ::GoogleDrive::Session.from_service_account_key(CREDENTIAL_PATH) if Jets.env.development?

    credential_json = RequestSecretsManager.request('/<project_name>/google-sa-credential')
    credential_hash = JSON.parse(credential_json)
    File.open("/tmp/#{CREDENTIAL_PATH}", 'w') do |f|
      JSON.dump(credential_hash, f)
    end
    ::GoogleDrive::Session.from_service_account_key("/tmp/#{CREDENTIAL_PATH}")
  end
end

# request_secrets_manager.rb
require 'aws-sdk-secretsmanager'

module RequestSecretsManager
  def self.request(secret_name)
    client = Aws::SecretsManager::Client.new(region: Jets.aws.region)
    get_secret_value_response = client.get_secret_value(secret_id: secret_name)
    get_secret_value_response.secret_string
  end
end

service

# spread_sheet_to_hash_service.rb

class SpreadSheetToHashService
  attr_reader :records

  SPREADSHEET_ID = '<Spreadsheet ID>'
  WORKSHEET_ID = '<Worksheet ID(The first page is 0)>'
  HEADER_COUNT = 1

  def initialize(session)
    @session = session
  end

  def run!
    worksheet = SpreadSheet.identify_worksheet_by_id(@session, SPREADSHEET_ID, WORKSHEET_ID)
    convert_worksheet_to_hash(worksheet)
  end

  private

  def convert_worksheet_to_hash(worksheet)
    #Skip header
    @records = worksheet.rows(HEADER_COUNT).map { |row|
      #Here, make the shape you like for each cell
      #row is a row, iterator row by row
      # row[0]:Value in first column
      # row[1]:Second column value
    }
  end
end

Spreadsheet operation module

# spread_sheet.rb

module SpreadSheet
  def self.identify_worksheet_by_id(session, spread_sheet_id, work_sheet_id)
    spreadsheet = session.file_by_id(spread_sheet_id)
    spreadsheet.worksheet_by_sheet_id(work_sheet_id)
  end
end

Summary

Now you can flexibly change the contents of the spreadsheet to json and return it as a response!

reference

gem google-drive