What I’m trying to do:
I require support to enhance my application with a feature that leverages Google Sheets as an alternative database. The feature must facilitate the synchronization of newly added database entries with a specified user’s Google Sheet automatically, even when the user is not actively logged in. This will enable updates to be made continuously and reliably by any user at any time. Although I’ve managed to access a Google Sheet via the built-in Google module during a logged-in sessions, I run into difficulties with server-initiated updates, particularly when the user is offline:
anvil.server.InternalError: Google request failed: The request is missing a valid API key.
Despite storing the refresh_token in the user database and attempting to refresh the access token using access_token = anvil.google.auth.refresh_access_token(refresh_token) , the problem persists. I’m hopeful for a straightforward solution, indicating a simple oversight rather than a complex issue.
What I’ve tried and what’s not working:
Code Sample:
My Code
# Define a function that saves the Google refresh token for a given user
@anvil.server.callable
def save_google_refresh_token(user, token: str):
# Get the user ID and print it
user_id = user.get_id()
print(f"USER ID: {user_id}")
# Get the user row from the users table and update the Google refresh token
user_row = app_tables.users.get_by_id(user_id)
user_row.update(google_refresh_token=token)
# Print the updated token
print(f"ROW token: {user_row['google_refresh_token']}")
# Define a function that tests getting a Google sheet
@anvil.server.callable
def test_get_sheet():
# Print the current user
print(f"current user: {anvil.users.get_user()}")
# Get the user with the specified email from the users table and get their Google refresh token
user = app_tables.users.get(email="user_email")
refresh_token = user["google_refresh_token"]
# Print the refresh token
print(f"REFRESH TOKEN: {refresh_token}")
# Initialize the access token to None
access_token = None
# If there is a refresh token, refresh the access token
if refresh_token:
access_token = anvil.google.auth.refresh_access_token(refresh_token)
print(f"ACCESS TOKEN: {access_token}")
# If there is no access token, print "NO TOKEN" and return
if not access_token:
print("NO TOKEN")
return
# Get the user's Google Drive folder and print its type
folder = anvil.google.drive.get_user_files()
print(type(folder))
# Loop through the files in the folder and print their titles
for f in folder.list_files():
if hasattr(f, "title"):
print(f.title)
# If there is an access token, get the user's refresh token and save it using the save_google_refresh_token function
if access_token:
refresh_token = anvil.google.auth.get_user_refresh_token()
save_google_refresh_token(user, refresh_token)
After some further discovery, I believe there may be an issue with the built in anvil.google.drive.get_user_file() as that is the line that seems to be throwing the error. A clue is that it says missing a valid API key. Since Google is oAuth2 based, it should not be asking for an API key.
To test my theory, I have bypassed the built-in functions and used the request function the reproduce my test.
See Code
My New Code
@anvil.server.callable
def test_get_sheet():
# Print the current user
print(f"current user: {anvil.users.get_user()}")
# Get the user's refresh token from the database
user = app_tables.users.get(
email="user@email.com"
) # Replace with your actual email
refresh_token = user["google_refresh_token"]
print(f"REFRESH TOKEN: {refresh_token}")
# If a refresh token exists, use it to get a new access token
if refresh_token:
refresh_data = {
"client_id": "my_client_id", # Replace with your actual client ID
"client_secret": "my_client_secret", # Replace with your actual client secret
"refresh_token": refresh_token,
"grant_type": "refresh_token",
}
headers = {"Content-Type": "application/x-www-form-urlencoded"}
response = anvil.http.request(
"https://oauth2.googleapis.com/token",
method="POST",
data=refresh_data,
headers=headers,
json=True,
)
# Get the new access token
access_token = response.get("access_token")
print(f"ACCESS TOKEN: {access_token}")
# If no access token was returned, exit the function
if not access_token:
print("NO TOKEN")
return
# Use the access token to get a list of files in the user's Google Drive
headers = {"Authorization": f"Bearer {access_token}"}
drive_response = anvil.http.request(
"https://www.googleapis.com/drive/v3/files", headers=headers, json=True
)
# Print the name of each file in the response
for item in drive_response.get("files", []):
print(item.get("name"))
# Get the new refresh token (if it was returned) and save it to the database
new_refresh_token = response.get("refresh_token")
if new_refresh_token:
print(f"NEW REFRESH TOKEN: {new_refresh_token}")
save_google_refresh_token(user, new_refresh_token)
My initial tests with this new version of code seem to work. I haven’t had a chance to test long term yet. I will continue to test, to see how this performs after a good period of time to see if things break down after the refresh token expires and such.
So far, my conclusion is that there is an issue with the anvil.google.drive built-in?
My understanding is that the Google Drive integration for User Files is intended to be used while the user is logged in. Just to be sure, are you following the docs here? Anvil Docs | Google Drive
I could be wrong, but I don’t think there’s a built-in way for a user to grant your app indefinite access to their Google Drive files after they’re no longer logged in to your app via anvil.google.drive.login().
An alternative might be to instead host the Google Sheets on the Google account associated with your app. Then you can manually share edit access for those sheets to users as needed so they can update them whenever.
Instruction about which part? Maybe it’s helpful to spell out further what I had in mind. These steps would need to be followed separately for each user:
Create a Google sheet specific to that user
Add it as an App File (choose “No client access” permissions for security)
Use a Data Table to track which App File names belong to which User (or you could hardcode that in a dict). When you want to access the data for that user, retrieve the correct App File name and then get the data. e.g., if you have the file names in a dict called file_names, you could use this code to get the desired file handle: getattr(app_files, file_names[user]).
I think I understand what you are thinking. Unfortunately, I don’t know if this will work. My app could have many thousands (or hopefully more) users and potentially multiple Google Sheets per user.