/gcf-sheets-api

Proxy JSON data in a Google Sheets spreadsheet and access it from your own domain. Fast deploy to Google Cloud Functions

Primary LanguageGoMIT LicenseMIT

Google Cloud Functions Sheets API

Get values from any public spreadsheet created with Google Sheets as JSON. This program is designed for Google Cloud Functions and requires very minimal set-up. But it can also be run from the command line as a stand-alone server for testing.

Generate an API key

Open the API & Services Credentials page on Google Cloud and generate a new API key with the "Create Credentials" button. Copy the generated key.

☁️ Installation on Google Cloud

You need to have a Google Cloud account with Cloud Functions and the Google Sheets API enabled to use this program. All you have to do is click "Enable" and wait a few minutes.

  1. Create a new Cloud Function
    Open the Cloud Functions dashboard and create a new function with the "Create Function" button. But before you click next..

  2. Add runtime variables
    This is where you add your app settings. You need to add 3 runtime variables with the "Add Variable" button at the bottom of the page.

    Name Value
    API_KEY The API key generated at the start of this guide
    SHEET_ID ID of the spreadsheet.
    Here's how to find it
    SHEET_RANGE A1 notation of the cells to export with this API

  3. Upload source code
    Download a Cloud Functions compatible ZIP of this program from the releases page. Select "ZIP Upload" under the "Source code" dropdown in the function creator

  4. Deploy
    Click "Deploy" and wait for the Cloud Function to start up. You should see the URL it created for your function under the function details page. The spreadsheet cells should now show up as JSON.

🖥️ Local installation

You can also run this program locally as a stand-alone server. There are currently no pre-built binaries of this program, but you can download the Go compiler and run it from the CLI.

  1. Clone this repo

    git clone https://github.com/VictorWesterlund/gcf-sheets-api.git

  2. Download Go
    Follow the instructions on go.dev. Or download it from a package manager

    sudo apt-get install golang-go

  3. Environment variables
    Copy the hidden .env.example file to .env and set the following values. Save and close when you're done

    Name Value
    API_KEY The API key generated at the start of this guide
    SHEET_ID ID of the spreadsheet.
    Here's how to find it
    SHEET_RANGE A1 notation of the cells to export with this API

  4. Start the server
    Start the web server with Go run

    go run .

    Note: The server will listen for HTTP (not HTTPS) connections on localhost:8090