Importing applicant data into Google Sheets

[BertalanD]

Use case

This year, we tracked applications as they were being sent in to ensure that CVs were in the expected format and all required documents were uploaded. We used Google Sheets to mark our review progress and whether we've received the corrections.

Someone had to regularly check Urán and manually add new rows as new applicants appeared. It would be useful to automate this and integrate Urán as a data source.

Possible solutions

There are multiple ways we could do this.

• The easiest is to provide an API endpoint that returns a csv file on a GET request (IMPORTDATA function).

  pros: simple, isn't Sheets-specific
  cons: no support for authentication (maybe a secret string as a query parameter?)

• Provide an authenticated API endpoint (e.g. OAuth) and use Google Apps Script to interact with it.

  pros: secure, more flexible
  cons: ties us to Google Sheets (e.g. doesn't work with Excel Online), still uses periodic polling

• Use the Google Sheets API to push new data from the server

  pros: no delay, don't have to worry about authentication
  cons: this requires the most code on our side, Google Sheets specific

(this is from a few minutes of research; there are probably other/better ways to do it)