← Back to Blog Home

Accessing Google Sheets with Ruby and Google Apps Script

Ruby
by: Andy Peterson
Andy Peterson

Article summary

For a recent project, I wrote a Ruby script to upload my team’s hours to a Google Spreadsheet. From time to time, I would have to manually edit the hours on the spreadsheet, so I left a note on those cells to remind myself that I changed the values. I needed my script to see these notes and know not to overwrite these cells. Unfortunately, the gem I usually use did not support access to these notes. I decided to look into how I could build my own Google Sheets API that my Ruby script could use, giving me a bit more flexibility for any other Ruby scripts I might create.

Creating an API

I ended up using Google Apps Script to create a fairly simple API for this task. This scripting language is similar to JavaScript, but it has access to various Google services, such as Google Docs and Google Sheets.

Once I created a new Google Apps Script file, I made the following endpoint:


function getCellNote(url, sheetIndex, row, column) {
  var ss = SpreadsheetApp.openByUrl(url);
  var sheet = ss.getSheets()[sheetIndex];
  var cell = sheet.getRange(row, column);
  return cell.getNote();
}

I used SpreadsheetApp to access the spreadsheet via url. Since the spreadsheet contains multiple sheets, this let me specify which particular sheet I want to edit. Within that sheet, I can also specify the row and column of the cell where I left my note.

After coding the API, I selected “Deploy as API Executable…” from the Publish menu. When you do this, a modal appears and guides you through deploying the API.

Authorizing

Next, I needed to enable my script and gather the proper credentials to access the endpoint. Detailed instructions can be found on Google’s quickstart guide, but there were three things I needed to do:

  1. Access the Developers console from the app script using the Resources menu.
  2. Turn on the API from the “Enable API” tab.
  3. Create credentials for the app and download the client_secret.json.

For the Ruby side, I needed to install the Google API Client gem:

gem install google-api-client

In order to access a user’s sheet on their Google Drive, the script has to gather the user’s credentials. Google provides Ruby code for this in their quickstart guide. I put this same code below, with a few changes to allow access to Google Spreadsheets as well as Google Drive.


require 'google/apis/script_v1'
require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'fileutils'

OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'
CLIENT_SECRETS_PATH = 'path/to/client_secret.json'
CREDENTIALS_PATH = File.join(Dir.home, '.credentials',
                             "my-app-credentials.yaml")
SCOPE = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']

def authorize
  FileUtils.mkdir_p(File.dirname(CREDENTIALS_PATH))

  client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)
  token_store = Google::Auth::Stores::FileTokenStore.new(file: CREDENTIALS_PATH)
  authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
  user_id = 'default'
  credentials = authorizer.get_credentials(user_id)
  if credentials.nil?
    url = authorizer.get_authorization_url(base_url: OOB_URI)
    puts "Open the following URL in the browser and enter the " +
         "resulting code after authorization"
    puts url
    code = gets
    credentials = authorizer.get_and_store_credentials_from_code(
      user_id: user_id, code: code, base_url: OOB_URI)
  end
  credentials
end

This prompts the user of the script with a url to follow and verify a Google account. A few key things to note:

  • The client_secret.json generated from the Developers Console should be stored somewhere convenient. I put mine in a creds folder in the same directory as my script.
  • SCOPE defines what we need authorization for. In this case, I want access to both Google Drive and Google Spreadsheets, so I added the appropriate Google API urls to that list.

Using the API Endpoints

After all that setup, it’s finally time to use the endpoints. In the same Ruby file as the authorization method, I added these four lines to specify which API the script should access:


SCRIPT_ID = 'MDsPuc46EcIuUIt77LQ6LC3Ac4yVd_8hJ'
SERVICE = Google::Apis::ScriptV1::ScriptService.new
SERVICE.client_options.application_name = "My Application Name"
SERVICE.authorization = authorize

(I got the SCRIPT_ID from the url of my Google Apps script.)

Finally, I could use the endpoint defined in the app script. Here’s the method I used to wrap the request to the endpoint:


def get_cell_note(spreadsheetUrl, sheetNum, cell)
  request = Google::Apis::ScriptV1::ExecutionRequest.new(
    function: 'getCellNote',
    parameters: [ spreadsheetUrl, sheetNum, cell ]
  )

  begin
    response = SERVICE.run_script(SCRIPT_ID, request)
    if response.error
      # Handle the error...
    else
      return response.response['result']
    end
  rescue Google::Apis::ClientError
    # Handle the error...
  end
end

Keep in Mind

A few things to note here:

  • function corresponds to the name of the function found in the Apps script.
  • parameters corresponds to the parameters for the function in the Apps script, so remember to keep the same order!
  • Make sure to handle the errors. I left the implementation details out for them, but it might be nice to either notify the user or log the issue, depending on your situation.

And that’s it! I can now access the notes on my Google Spreadsheets. By setting up this API, I can also create other endpoints, allowing my Ruby scripts to access all of my Google docs.

Related Posts