Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
carlito
Frequent Visitor

Various Errors when Refreshing Google Sheets Data

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

 

 

1 ACCEPTED SOLUTION
stretcharm
Memorable Member
Memorable Member

 

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

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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
Frequent Visitor

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

peter_rips
Frequent Visitor

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

 

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
Not applicable

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
Helper III
Helper III

Has anyone found a solution?

wittertj
Regular Visitor

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
Regular Visitor

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

Anonymous
Not applicable

Hi there,

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

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

stretcharm
Memorable Member
Memorable Member

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.