I’ve utilized the Google Sheets pack to integrate and surface tables from a google sheet into coda tables.
The tables in google sheets have a variety of formatting with blank rows and/or partially blank rows that, ideally, would be able to avoid those from pulling over into coda.
Any advice on how to accomplish that with the Google Sheet pack/integration?
Example:
Row 1 - all columns have data
Row 2 - all columns have data
Row 3 - 2 columns have data (i.e. are incomplete)
Row 4 - all columns have data
Row 5 - blank
Row 6 - all columns have data
What I want to surface in coda, ideally, without me have to clean up all the various tables I’m trying to integrate from Google sheets is, in the above example, just rows 1, 2, 4, and 6.
Hi HG_3 – If you know where the incomplete rows are located in the sheet, you can sync by named regions (i.e. A1:B30), but it doesn’t look like it is possible to calculate those regions via a formula that checks whether they are complete.
However, once the data has been pulled in you could sync your view of the table to only show complete lines. So I think the best solution depends on your use case. Do you want to elaborate a little about what the structure of your Google Sheet is and what you hope to do with it in Coda?
Thanks, @Traci_Larson , for taking the time to reply.
Unfortunately, these are legacy google sheets with a mix of use of empty rows across many sheets and tables. Thus, there’s no way to easily pre-defined which regions to sync as those regions can expand as new rows are added.
I’m working to create a union of a variety of google sheet tables (tabs) into a master table in coda. Thus I’m:
syncing the google tables to a table in coda
creating a union of the relevant tables in coda
With step 1, the sync will sometimes return that it met the default 1000 row limit due to the empty row (I can see in the source table there are less rows with actual content). While I can expand to 10000 rows to sync, it creates downstream performance issues when creating a union as it’s having to parse all of the empty rows. I’ve attempted creating views of those synced google tables that filters out the empty rows but it still doesn’t solve for the sync/integrations row limitations + the downstream impact.
Thus why I was hoping there was a solution I wasn’t aware of to have an open range that just ignores empty rows with the coda/google sheet pack integration.
The ideal solution would be to delete the empty rows in Google sheets, not sure if it is possible.
However, Coda can.
What I suggest you do, is add a formula column to the end of your table. That formula will check if all the columns isBlank(). You can then filter where the result is true, and delete those rows.
You could also create a button that would filter the table, and automatically delete the entries.
@Piet_Strydom suggestion is a good one, and the only option if you have little or no control of the Google sheet.
It sounds like the Google sheet is constantly being added to and is being used for other workflows, so you cannot necessarily change the source data.
However if possible, I would write an app script to copy only the data you want into a separate sheet on the same (or different) Google spreadsheet and have coda pull from the filtered list (minus the blank rows).
That way you will avoid the blank rows taking up the 10,000 ingestion limit and also prevent any performance issues on coda side trying to index the related blank table rows unnecessarily.