Upload to google sheet

Anvil Q&A
1

is there any way to upload my data frame to a google sheet. Any kind of help would be
appreciated.

2

or any way to upload an excel file to a google sheet

3

There are several ways to do this, but have you tried the Google Sheets tutorial first to see if you can work it through?

4

yeah i did but can you tell me how any way on how can i put my dataframe in google spreadsheet which i have generate in my Google API in anvil
like how can i just move all my dataframe to the empty google spreadsheet which i have loaded in anvil app_files

5

I seem to be running into the same issue. I am able to create a new Google Sheet but am struggling to “add rows”.

I have tried adding fields to my Google Sheet like this:

# this is a method within a util class I have
def add_rows(self, worksheet: Worksheet, row_data: Union[List[dict], dict]) -> int:
        if not isinstance(worksheet, Worksheet):
            raise TypeError("worksheet must be of type anvil.google.sheets.Worksheet")
        elif not isinstance(row_data, dict):
            raise TypeError("row must be of type dict")

        if isinstance(row_data, dict):
            row_data = [row_data]

        for row in row_data:
            worksheet.add_row(**row)

When adding my first row (my Fields), I have tried formatting them into a dict like this:

field_row = {field: field for field in fields}
self.add_rows(worksheet, field_row)

I have even tried adding them by cell:

# this is a method within my util class
def _add_fields(self, worksheet: Worksheet, fields: List[str]):
        if not isinstance(worksheet, Worksheet):
            raise TypeError("worksheet must be of type anvil.google.sheets.Worksheet")
        elif not isinstance(fields, list):
            raise TypeError("fields must be of type list")

        for i, field in enumerate(fields, start=1):
            cell = worksheet.get_cell(1, i)
            cell.value = field

This method has been the most successful, but I always get an Anvil Server Timeout error with this method. I also, tried putting this into a background task, but then the User Auth is lost and I get a Permissions error.

I hope there is a simple solution to this.

Inviting some smart folks to this quandary: @stucork @brooke @ian_anvil

6

Hey @chad63e!

Wish I could go into more detail, but here are a couple of thoughts:

DataFrame to Compatible Format for Google:
Before uploading the data to Google Sheets, ensure your DataFrame is in a format compatible with Google Sheets. You can convert your DataFrame to a list of dictionaries, where each dictionary represents a row in your DataFrame.

Bathc operations:
Uploading data row-by-row can be slow and might lead to timeout errors. Instead, use batch operations to upload multiple rows at once. This reduces the number of API calls and can help avoid timeout issues.

Multiple Batches
If your DataFrame is very large, consider splitting it into smaller chunks and uploading these chunks sequentially. This approach can help manage memory usage and avoid timeouts.

User Authentication in LONG Background Tasks:
If using a background task leads to permission errors due to user authentication issues, you might need to store and use authentication tokens or credentials in your background task to maintain the necessary permissions. Possibly re-authenticate after the token timeout period if that’s taking too long (i.e. if you know the token will only last for 3600s then re-authenticate at 3300s).

Hopefully some of that is helpful!

7

I have found this to be very true, in-fact I usually pivot all of my data into lists of columns in row order and upload a column at a time, sequentially.

Google likes this better for some reason when loading an entire sheet.
(Also, don’t forget to first create the correct number of blank rows in the google sheet that match your total row count before you start uploading each column)

I do not use anvil for this though, I use the Google API directly with OAUTH and the gspread python library.

8

Ian, thanks for the input. However, my concern is that it appears that unless there is already an established header (fields), you are not able to use the add_row() method? I’m sure it’s just user error, but I have tried every way I can imagine getting it to work.

I have tried setting the fields property with the new header fields (which seems to work) then populate the first row with those corresponding fields as such:

worksheet = anvil.google.sheets.Worksheet
worksheet.fields = ["Col 1", "Col 2", "Col 3"]
worksheet.add_row({"Col 1": "Col 1", "Col 2": "Col 2", "Col 3": "Col 3"})

But, even that will not populate the first row.

Unfortunately, for now I’m looking to abandon the Anvil built-in method and use Google’s API instead.

9

Anvil built in processes can be tricky. I’ve often ended up sending the data through POST to n8n.io which is a low code automation platform that is great for ETL and reverse ETL (Extract, Transform, Load).

I’ll frequently use it to easily put the data in Gsheets (or other tools).

1 Like