Do you need Omni data in Google Sheets?
If you have GCP and the ability to create a cloud function, this article is for you. We’ll be extending the pattern to AWS and Azure soon.
Connor wrote an excellent article on how to use Zapier to schedule data to google sheets, but not every organization has Zapier and you might be looking for something more scalable and cheaper.
We’ll first show how to use it, and below will be the GCP setup guide.
Usage
We’ll start with how to use it, watch the video or follow the text based usage instructions just after. Full initial setup instructions below under Initial Technical Setup.
Step 1) Collect the following info from your google sheet:
Step 2) Assemble the url and arguments
Your endpoint for the function will have a url associated with it, use that and the information to assemble the full url with arguments (and no line breaks):
https://{{cloud function endpoint url}}?
spreadsheet_id={{spreadsheet_id obtained from the google sheets url}}
&mode=new #if you want a new sheet daily
&sheet=my%20custom%20sheet #if you desire a custom name
Step 3) Configure a scheduled delivery in Omni
Apply your URL and select webhook destination:
Ensure json is the selected delivery format:
Initial Technical Setup (GCP console – easy mode)
This is the easiest recipe for installing a cloud function on GCP and applying the right logic. This assumes you have owner / admin privileges and are in in a GCP project with billing enabled.
Step 1) Enable the Google Sheets API
Search for google sheets in the search bar and ensure the api is enabled
Step 2) Create a service account
Search in the console for service accounts and go to this result “Service Accounts IAM & Admin”
Click “Create Service Account”
As you give it a name, copy that email (it can easily be obtained later too). It is the email your users will need to share their sheet with. After submitting the form you’ll be back on the service accounts page, click into the newly created service account which will take you to its admin page.
On the page for your service account, click “keys” and then “add key”. Choose the json option.
Creating the key will automatically download it to you computer. Change the name of the file to service_account.json
Step 3) Create the cloud function
Search for cloud functions and you will be taken to this page. Click “write a function”.
Choose the options for write an inline function, and a python 3.12 runtime
You’ll be taken to an editor screen.
Replace the contents of main.py with this:
import functions_framework
import os
from datetime import datetime
import logging
import google.auth.credentials
from google.oauth2 import service_account
if os.getenv('GOOGLE_APPLICATION_CREDENTIALS') is None:
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'service_account.json'
import google.auth
from googleapiclient.discovery import build
def get_sheets_client(rw_vs_ro: str):
"""
Generate the service credentials to be used to query a google sheet
:param rw_vs_ro: A string, 'r_o' or 'r_w', representing whether creds should be readonly or readwrite
:return: The built service variable
"""
if rw_vs_ro == 'r_o':
scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']
if rw_vs_ro == 'r_w':
scopes = ['https://www.googleapis.com/auth/spreadsheets']
creds, project = google.auth.default(scopes=scopes)
service = build('sheets', 'v4', credentials=creds)
return service
def transform_data(webhook_data, header_row=False):
"""
Transform the incoming webhook data into a format suitable for Google Sheets.
Args:
webhook_data (dict): The incoming webhook data.
Returns:
list: A list of transformed data rows.
"""
new_values = []
if header_row:
new_values = [[str(key) for key in webhook_data[0].keys()]]
new_values.extend( [[val for val in row.values()] for row in webhook_data])
else:
new_values = [[val for val in row.values()] for row in webhook_data]
return new_values
@functions_framework.http
def omni_to_sheets(request):
"""
Process incoming webhooks and write data to Google Sheets.
Args:
request (flask.Request): HTTP request object.
Returns:
The response text or any set of values that can be turned into a
Response object using `make_response`
"""
# Debug environment variable
creds_path = os.environ.get('GOOGLE_APPLICATION_CREDENTIALS')
logging.info(f"Credentials path: {creds_path}")
# Check if file exists
if creds_path and os.path.exists(creds_path):
logging.info("Credentials file exists!")
else:
logging.error("Credentials file not found!")
# Validate request method
if request.method != 'POST':
return ('Method not allowed', 405)
try:
webhook_data = request.json
# Spreadsheet configuration
spreadsheet_id = request.args.get('spreadsheet_id')
mode = request.args.get('mode', 'append')
sheet_name = request.args.get('sheet', 'Sheet1')
range_name = f"{sheet_name}!A1:Z"
# Get Sheets client
sheets_client = get_sheets_client('r_w')
if not sheets_client:
return ('Failed to create Sheets client', 500)
if mode == 'new':
sheet_name = f'{sheet_name} {datetime.now().strftime("%Y-%m-%d")}'
range_name = f"{sheet_name}!A1:Z"
try:
batch_result = sheets_client.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body={
'requests': [{
'addSheet': {
'properties': {
'title': sheet_name,
}
}
}]
}
).execute()
new_sheet_id = batch_result['replies'][0]['addSheet']['properties']['sheetId']
print(f'Created new sheet with ID: {new_sheet_id}')
except Exception as e:
print(f"Error creating new sheet, appending to existing sheet: {sheet_name}")
sheet_check = sheets_client.spreadsheets().values().get(
spreadsheetId=spreadsheet_id,
range=range_name
).execute()
existing_values = sheet_check.get('values', [])
sheets_client.spreadsheets().values().append(
spreadsheetId=spreadsheet_id,
range=range_name,
valueInputOption='RAW',
insertDataOption='INSERT_ROWS',
body={
'values': transform_data(webhook_data, header_row=not existing_values)
}
).execute()
return ('Webhook processed successfully', 200)
except Exception as e:
# Log the full error for debugging
print(f"Error processing webhook: {e}")
return ('Internal server error', 500)
# Optional: Error handler for debugging
@functions_framework.http
def error_handler(request):
"""
Custom error handler to help with debugging deployment issues.
"""
try:
# Simulate an error to test error handling
raise Exception("Test error handling")
except Exception as e:
print(f"Caught error: {e}")
return (str(e), 500)
Change the entry point function to omni_to_sheets
Change the contents of requirements.txt to this:
blinker==1.9.0
cachetools==5.5.2
certifi==2025.1.31
charset-normalizer==3.4.1
click==8.1.8
cloudevents==1.11.0
deprecation==2.1.0
Flask==3.1.0
functions-framework==3.8.2
google-api-core==2.24.2
google-api-python-client==2.166.0
google-auth==2.38.0
google-auth-httplib2==0.2.0
google-auth-oauthlib==1.2.1
googleapis-common-protos==1.69.2
gunicorn==23.0.0
httplib2==0.22.0
idna==3.10
itsdangerous==2.2.0
Jinja2==3.1.6
MarkupSafe==3.0.2
oauthlib==3.2.2
packaging==24.2
proto-plus==1.26.1
protobuf==6.30.2
pyasn1==0.6.1
pyasn1_modules==0.4.1
pyparsing==3.2.3
requests==2.32.3
requests-oauthlib==2.0.0
rsa==4.9
uritemplate==4.1.1
urllib3==2.3.0
watchdog==6.0.0
Werkzeug==3.1.3
click the + icon on the upper right of the file selector and create a file called service_account.json
paste the contents of your service_account.json file you downloaded when we created the service account key.
Click the blue button “Save and redeploy” and the service will be built.
On this screen while it’s deploying you may grab the endpoint url. It’s the url your users will need for the usage portion.
Elite Setup (command line)
… Coming Soon …
This elite setup will show how to create the function from the command line and VScode (using google’s function framework), which can also have an ngrok local redirect, and access the debugger so you can customize, develop and test in style.