I know there are a few threads on some recurring issues with regard to running a Look through the Google Scripts functionality to dynamically write data to a Google Sheet. There have been a few updates in terms of what Google Sheets accepts and some updates in regard to the syntax accepted in Google Scripts. While the examples may intermittently work, Looker’s Department of Customer Love does not currently support these. We strongly recommend using the native Google Sheet Actions. More information on these actions can be found here. The action is currently supported by our development team and the Department of Customer Love can help you troubleshoot potential issues.
If there are any functionalities that the current action is missing, please use our Pendo feedback app by clicking the question mark in the top right corner of the UI and selecting “Product Idea?”. This allows you to directly submit feedback to our product team. If you do not see the option for Product Idea, no worries, feel free to contact our support team and they can pass on the feedback.
If you cannot use our native action and would like to customize the workflow in terms of sending data to a Google sheet. The most robust method would be to use Looker’s REST API and the native Google Sheets API. This will give your development team full control over the process.
A common workflow would be to use the Run Look endpoint and write the data as a CSV. Then, use the Google Sheets API (gspread library in python) to write this data to a sheet. This can be set up to run on a regular basis with the use of a cron job or a similar function. The example below leverages Looker’s Python SDK with Google Sheets API. With the Google Sheet API there is even the functionality to write to specific tabs in a sheet.
To use the Google Sheets API, you must generate credentials in the Google Console. This video has a great walk through of how to enable the API and a basic set up of the core functions in the gspread Python library.
import pandas as pd
# from typing import cast, MutableSequence, Sequence
####Initialize API/SDK for more info go here: https://pypi.org/project/looker-sdk/
from looker_sdk import methods40, models40
sdk = looker_sdk.init40() # or init40() for v4.0 API
me = sdk.me()
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://www.googleapis.com/auth/drive.file',
## Run look and write data as a CSV
look = sdk.run_look(look_id=1995,result_format="csv")
file = open('read.csv','w')
# the csv is stored in the local directory
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
# you must give share the Google sheet with the service account. In the example the name of the #service account is TEST_SHEET
sheet = client.open("TEST_SHEET")
# data = sheet.get_all_records()
content = open('read.csv', 'r').read()
#For further learning on the functions try Tech with Tim's Video
#https://www.youtube.com/watch?v=cnPlKLEGR7E or consult the google documentation
Both of these are frameworks your team may be able to leverage to create their own seamless workflow to integrate Looker with Google Sheets, but again we do have a native action that allows Looker users to conduct the same actions without any of the code or development overhead.
The code above is an example framework or outline of what can be achieved with Looker’s API and the Google Sheets API. Feel free to customize this -- write to different sheets based on the Look ID, conditionally write to a sheet based on the number of rows returned by Looker. The customization is up to you. Let us know in the comments more about your team’s desired workflow to integrate Looker with Google Sheets.
Please let us know if you have any questions!