Various Errors when Refreshing Google Sheets Data

[carlito]

Hey Everyone,

I have a report that draws in data from a Google Sheet (using the web connector method), and when I refresh, I'll often get a variety of error messages, despite the preview loading just fine. The error messages I get are:

- "Expression.Error: The column 'column name' of the table wasn't found."
- "The 'column name' column does not exist in the rowset."
- "OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E."
- "OLE DB or ODBC error: [Expression.Error] The field 'column name' of the record wasn't found.."

What's most confusing these messages seem to cycle through—I can try to refresh the same query a few times and get a different message each time (however, the Expression.Error column not found message is most common); sometime's i'll get no errors and the data will refresh just fine. This is without making any changes to the Google Sheet, credentials or Query.

Further, the column identified in the error changes each time as well (ie sometime it'll be 'column1', other times it'll be 'column2'), and there doesn't seem to be any relationship between an error message and the column listed. I've even tried removing all additional steps in the Query Editor beyond navigation and promoted headers, but I still have the same issue.

Anyone have any idea what could be causing this?

Thanks in advance!

- C

[Anonymous]

I've found this seems to work for me as I was getting the missing column error all the time (Maybe 1 in 10 refreshes would work. 

Warning: This is a bit of a faff so it won't be practical if there are lots of tabs in one document.

I take the google sheet URL:

https://docs.google.com/spreadsheets/d/randomseriesofcharacters/edit#gid=984654321

The gid=984654321 denotes the individual tab.

I change the URL to this

https://docs.google.com/spreadsheets/d/randomseriesofcharacters/export?format=csv&gid=984654321

This will load the individual tab into Power BI through the web connector - like I said not manageable to have so many data sources if you have data in lots of tabs but for individual tabs it's working fine for me. 

[HeliosOne]

I have this issue in PBIservice too. Sometimes instead of any error PQ just makes wrong merging.

[peter_rips]

Same issue here.

Refresh Data doesn't work (the column 'column name' of the table wasn't found.

On the next refresh, will be another table/same error.

Case we do a manual refresh data - table by table instead of a "refresh all"  - works perfectly, without this error.

Thanks

[JeffJ]

I'm having this problem too.

I don't see the option to do a table-by-table refresh.  If this workd around it I'm willing to do it.  Where did you find that?

[Anonymous]

You can choose which queries / tables you want to refresh by going into Query Editor and right clicking on the different tables. Just select / unselect the option for "Include in Report Refresh" as needed. 

Just don't forget you have that option applied.

[mpalha04]

Has anyone found a solution?

[wittertj]

Any chance anyone has found a fix or at least the cause of this issue? It seems to have started to happen with out PBI files recently as well.

[wittertj]

Any chance anyone has found a solution to this? I have also recently started experiencing the same issue.

[Anonymous]

Hi there,

did you ever figure this out? I have the exact same problem.

[carlito]

Unfortunately not, but i've stopped experiencing the issue. Not really sure what changedthough

[stretcharm · Accepted solution]

I've not connected to Google sheets, but Steph Loche shows how to use R to connect here. Maybe this works better.

https://www.youtube.com/watch?v=8lWR-_L52Qs