Example - Google Sheets API

An example of setting up a basic Python API to import/export data from/to Roda and communicate with Google Sheets.

PrerequisitesGoogle Sheets CredentialsCreate the API

Install Python (set PATH while installation)
Install VS Code
Obtain Google Sheets Credentials (see next Tab)

Obtaining Google Sheets Credentials File and Required Permissions

Create a Google Cloud Project
- Go to the Google Cloud Console
- Sign in with your Google account (ensure it’s the account you’ll use for Google Sheets access).
- Click the "Select a Project" button (top left) and click "New Project" in the popup.
- Name your project (e.g., "RodaSheetsAPI"), click "Create," and wait for it to set up.
Enable the Google Sheets API
- In the Google Cloud Console, navigate to "APIs & Services" > "Library".
- Search for "Google Sheets API", select it, and click "Enable" to activate it for your project.
Create a Service Account
- Go to "APIs & Services" > "Credentials" in the Google Cloud Console.
- Click "+ Create Credentials" > "Service account".
- Enter a "Service account name" (e.g., "RodaServiceAccount").
- Click "Create and Continue".
- Skip the "Grant this service account access to project" step (optional permissions can be added later if needed).
- Click "Done" to create the service account.
- You’ll see the service account listed. Note the email address, you’ll need it later.
Generate a JSON Key for the Service Account
- From the service account list, click on the service account you created.
- Go to the "Keys" tab, then click "Add Key" > "Create new key".
- Select "JSON" as the key type and click "Create".
- A JSON file will download automatically. Save this file securely.
- This JSON file contains the credentials (client email, private key, etc.) needed for your Python script to authenticate with Google Sheets.
Share the Google Sheet with the Service Account
- Go to Google Sheets and create a new file (Blank spreadsheet).
- Note the sheetId from the url: https://docs.google.com/spreadsheets/d/THIS_PART/edit
- Click the "Share" button (top-right corner).
- Add the service account email (service account email we noted earlier) as an editor.
- Set the permission to "Editor"
- Click "Send" or "Done" to grant access.
Required Permissions and Rights
- Use the scope https://www.googleapis.com/auth/spreadsheets for read/write access to Google Sheets.
- In the Python script (see next Tab), ensure the scope variable includes the above value when authorizing with gspread:
```
scope = ['https://www.googleapis.com/auth/spreadsheets']
```
Use the Credentials in the Python Script
- Update your PATH_CREDENTIALS in the Python script to point to the JSON file’s location (e.g., c:/path_to_creds/credentials_file_we_downloaded_earlier.json).
Done
- Set up the script (next tab) and run your API.

Create a folder for the API (e.g.: c:\dev\roda-api)
Open that Folder in VS Code
Open Terminal in VS Code and create the python virtual environment
```
python -m venv venv
```
Run the virtual environment
```
venv\Scripts\activate
```
Install the required libraries
```
pip install flask gspread oauth2client
```
Inside your api folder (e.g.: c:\dev\roda-api) create the a python file (e.g.: api.py)

Paste in the code and adjust the variables as needed:

api.py

import json  # Imports the JSON library for handling JSON data
import gspread  # Imports the gspread library for interacting with Google Sheets
from flask import Flask, request, jsonify, make_response  # Imports Flask components for creating the API
from oauth2client.service_account import ServiceAccountCredentials  # Imports credentials handler for Google Sheets authentication

# Adjustable variables for users to customize the API
PATH_CREDENTIALS = 'c:/path_to_creds/credentials_file_we_downloaded_earlier.json'  # Set the file path for Google Sheets credentials
SPREADSHEET_KEY = 'SPREADSHEET_ID_HERE'  # Adjust the Google Sheets spreadsheet ID
HOST_PORT = 5000  # Specifies the port number for the Flask app to run on

app = Flask(__name__)  # Creates a Flask application instance for the API

# Set up Google Sheets client for authentication and access
scope = ['https://www.googleapis.com/auth/spreadsheets']  # Defines the scope for Google Sheets API access (read/write)
credentials = ServiceAccountCredentials.from_json_keyfile_name(PATH_CREDENTIALS, scope)  # Loads credentials from the JSON file with the specified scope
gc = gspread.authorize(credentials)  # Authorizes gspread to use the credentials for Google Sheets interactions

# Import Google Sheets data to Roda as JSON via GET request
@app.route('/import', methods=['GET'])
def import_sheets_to_roda():
    print('Importing data from Google Sheets')
    print("\n---------- HTTP Headers ----------")
    print(request.headers)

    try:
        sheet = gc.open_by_key(SPREADSHEET_KEY).sheet1  # Opens the Google Sheet using the spreadsheet key and first sheet
        all_data = sheet.get_all_values()  # Retrieves all values from the Google Sheet as a list of rows

        print("\n---------- All Data from Google Sheets ----------")
        print(all_data)

        if all_data:  # Checks if there’s any data in the sheet
            headers = all_data[0]  # Assigns the first row as headers
            data = all_data[1:]  # Assigns remaining rows as data, excluding headers
        else:  # Handles an empty sheet
            headers, data = [], []  # Sets empty lists for headers and data if no data exists

        roda_data = []  # Initializes an empty list to hold data
        print("\n---------- Headers ----------")
        print(headers) 

        for row in data:  # Iterates through each row of data
            if len(row) == len(headers) and any(cell.strip() for cell in row):  # Ensures the row length matches headers and contains non-empty data
                row_dict = {headers[i]: row[i] for i in range(len(headers)) if row[i].strip()}  # Creates a dictionary from headers and row values, excluding empty values
                if row_dict:  # Ensures the dictionary isn’t empty
                    roda_data.append(row_dict)  # Adds the dictionary to the Roda data list

        print("\n-------------- Roda Data -----------------")
        print(roda_data)

        return jsonify(roda_data), 200  # Returns the Roda data as JSON with a 200 OK status
    except Exception as e:  # Catches any errors during the import process
        return jsonify({'status': 'error', 'message': str(e)}), 500  # Returns an error response with the exception message and a 500 Internal Server Error status

# Export Roda JSON data to Google Sheets via POST request
@app.route('/export', methods=['POST'])
def export_data_to_sheet():
    print("\n---------- HTTP Headers ----------")
    print(request.headers)

    data = request.get_json()  # Retrieves JSON data from the POST request body
    if not data:  # Checks if no data was provided
        return make_response(jsonify({"error": "No data provided"}), 400)  # Returns a 400 Bad Request response if data is missing

    try:
        sheet = gc.open_by_key(SPREADSHEET_KEY).sheet1  # Opens the Google Sheet using the default spreadsheet key and first sheet
        sheet.clear()  # Clears all existing data in the sheet before exporting new data

        print("\n---------------- Data for Export ----------------")
        print(data)

        # Standardize data format
        if isinstance(data, dict):  # Checks if the data is a dictionary
            data = list(data.values())  # Converts the dictionary values into a list of dictionaries for processing

        if isinstance(data, list) and all(isinstance(item, dict) for item in data):  # Ensures data is a list of dictionaries
            headers = sorted(data[0].keys()) if data else []  # Extracts and sorts headers from the first dictionary, or sets empty if no data
            print("\n-------------- Headers -----------------")
            print(headers)

            rows = [[item.get(header, "") for header in headers] for item in data]  # Creates a list of rows from the data, filling missing values with empty strings
            print("\n-------------- Rows -----------------")
            print(rows)
        else:  # Handles invalid data format
            return make_response(jsonify({"error": "Invalid data format"}), 400)  # Returns a 400 Bad Request response if data format is wrong

        # Prepare complete data to upload in one call
        complete_data = [headers] + rows  # Combines headers and rows into a single list for Google Sheets
        sheet.append_rows(complete_data, value_input_option='USER_ENTERED')  # Appends all rows to the Google Sheet, preserving formats as entered by a user

        return jsonify({"success": True, "message": "Data exported successfully"})  # Returns a success response with a confirmation message
    except Exception as e:  # Catches any errors during the export process
        return jsonify({'status': 'error', 'message': str(e)}), 500  # Returns an error response with the exception message and a 500 Internal Server Error status

if __name__ == '__main__':  # Entry point for running the Flask app
    app.run(debug=True, port=HOST_PORT)  # Starts the Flask application in debug mode on the specified port

Run the API
```
python .\api.py
```
You can now use the endpoints to export/import data in Roda
- http://localhost:5000/export
- http://localhost:5000/import
- Test the export
- Change some data in your spreadsheet and test the import