SOLVED
Home

Excel "GetData" from file or web (OneDrive)

%3CLINGO-SUB%20id%3D%22lingo-sub-857687%22%20slang%3D%22en-US%22%3EExcel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857687%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20import%20data%20from%20a%20shared%20file%20via%20OneDrive%20(into%20another%20shared%20file%20via%20OneDrive).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20two%20different%20options%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Using%20%22GetData%22%20from%20a%20file.%20This%20works%20if%20I%20am%20using%20my%20computer.%20The%20moment%20I%20use%20another%20computer%20with%20access%20to%20the%20same%20Drive%2C%20I%20have%20to%20re-link%20the%20data%20from%20the%20new%20computer%20to%20the%20same%20file.%20I%20even%20changed%20the%20names%20of%20the%20multiple%20computers%20to%20the%20same%20name%2C%20so%20they%20have%20the%20same%20%22filepath%22.%20It%20is%20still%20not%20working%20(without%20re-linking%20the%20same%20workbook%20when%20on%20a%20different%20computer).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Using%20%22GetData%22%20from%20a%20excel%20link%20on%20OneDrive.%20I%20tried%20multiple%20links%2C%20the%20one%20that%20seems%20to%20work%20is%20%22downloading%22%20the%20file%2C%20and%20copying%20the%20download%20link.%20This%20only%20works%20until%20the%20file%20is%20changed%20and%20a%20new%20link%20makes%20the%20old%20download%20link%20obsolete.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EPlease%20advise%20on%20how%20I%20can%20use%20either%20methods%20to%20link%20the%20data%2C%20without%20having%20to%20update%20the%20links%20(or%20files)%20every%20time%20the%20sheet%20is%20opened%20from%20another%20computer%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%20I%20have%20spent%20countless%20hours%20trying%20to%20resolve%20this%20issue%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-857687%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGetData%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-858930%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858930%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409782%22%20target%3D%22_blank%22%3E%40emilychang87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Bhas%20this%20%3CA%20href%3D%22https%3A%2F%2Faccessanalytic.com.au%2Fconnect-to-files-on-onedrive-and-sharepoint%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EVIDEO%3C%2FA%3E%20that%20might%20help%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECuong%3C%2FP%3E%0A%3CP%3EMicrosoft%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859370%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859370%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20you%20please%20relink%3F%20I%20am%20getting%20a%3A%26nbsp%3B%3CSPAN%3E504%20Gateway%20Time-out%20%3CFONT%20size%3D%223%22%3Emessage....%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you!%20%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320924%22%20target%3D%22_blank%22%3E%40cuong%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859524%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859524%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%20can%20you%20help%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859706%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859706%22%20slang%3D%22en-US%22%3EStrange%2C%20try%20this%20direct%20link%20to%20YouTube%20instead%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FgJF2e_43FRY%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FgJF2e_43FRY%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859723%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859723%22%20slang%3D%22en-US%22%3EAnd%20an%20article%20here....%20%3CA%20href%3D%22https%3A%2F%2Fssbipolar.com%2F2018%2F10%2F29%2Fpower-query-connecting-to-excel-workbooks-in-sharepoint-online%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fssbipolar.com%2F2018%2F10%2F29%2Fpower-query-connecting-to-excel-workbooks-in-sharepoint-online%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862191%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20link!%20I%20went%20to%20the%20source%20file%20and%20clicked%20%22copy%20link%20to%20clipboard%22%2C%20used%20the%20link%20to%20fetch%20the%20data%20from%20web%2C%20and%20received%20the%20following%20error%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Unable%20to%20Connect%3CBR%20%2F%3EWe%20encountered%20an%20error%20while%20trying%20to%20connect.%3C%2FP%3E%3CP%3EDetails%3A%20The%20downloaded%20data%20is%20HTML%2C%20which%20isn't%20the%20expected%20type.%20The%20URL%20may%20be%20wrong%20or%20you%20might%20not%20have%20provided%20the%20right%20credentials%20to%20the%20server.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20my%20link%20looks%20like%20copied%20from%20the%20source%20file%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fd.docs.live.net%2Fbb9887e258ca6c3a%2FGSD%2520(M%2520One%2520Drive)%2FOrder%2520Processing%2FLabel%2520Request%2520Form%2520Pivot.xlsx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fd.docs.live.net%2Fbb9887e258ca6c3a%2FGSD%2520(M%2520One%2520Drive)%2FOrder%2520Processing%2FLabel%2520Request%2520Form%2520Pivot.xlsx%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmily%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862547%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862547%22%20slang%3D%22en-US%22%3EHi%20Emily%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%E2%80%99re%20using%20OneDrive%20personal%20then%20this%20link%20should%20help%20(%20my%20solution%20was%20for%20OneDrive%20Business)%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FPower-Query%2FPowerBI-support-for-OneDrive-Personal-as-data-source%2Ftd-p%2F129125%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FPower-Query%2FPowerBI-support-for-OneDrive-Personal-as-data-source%2Ftd-p%2F129125%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898162%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898162%22%20slang%3D%22en-US%22%3E%3CP%3ESO%20sorry%20for%20the%20late%20response...%20was%20out%20of%20town.%20Your%20solution%20worked!%20Thank%20you%20so%20much!%20You%20saved%20my%20excel%20life%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898770%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20%22GetData%22%20from%20file%20or%20web%20(OneDrive)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898770%22%20slang%3D%22en-US%22%3EGlad%20I%20could%20help%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello! 

 

I am trying to import data from a shared file via OneDrive (into another shared file via OneDrive).

 

I have tried two different options:

 

1) Using "GetData" from a file. This works if I am using my computer. The moment I use another computer with access to the same Drive, I have to re-link the data from the new computer to the same file. I even changed the names of the multiple computers to the same name, so they have the same "filepath". It is still not working (without re-linking the same workbook when on a different computer).

 

2) Using "GetData" from a excel link on OneDrive. I tried multiple links, the one that seems to work is "downloading" the file, and copying the download link. This only works until the file is changed and a new link makes the old download link obsolete.

Please advise on how I can use either methods to link the data, without having to update the links (or files) every time the sheet is opened from another computer, etc.

 

Thank you so much! I have spent countless hours trying to resolve this issue

 

 

9 Replies
Highlighted

Hi @emilychang87 

 

@Wyn Hopkins has this VIDEO that might help

 

Cuong

Microsoft

 

Highlighted

Can you please relink? I am getting a: 504 Gateway Time-out message....

Thank you! @cuong 

Highlighted
Highlighted
Strange, try this direct link to YouTube instead

https://youtu.be/gJF2e_43FRY
Highlighted

@Wyn Hopkins Thanks for the link! I went to the source file and clicked "copy link to clipboard", used the link to fetch the data from web, and received the following error:

 

"Unable to Connect
We encountered an error while trying to connect.

Details: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

 

This is what my link looks like copied from the source file:

https://d.docs.live.net/bb9887e258ca6c3a/GSD%20(M%20One%20Drive)/Order%20Processing/Label%20Request%...

 

Thanks,

 

Emily

Highlighted
Solution
Hi Emily

If you’re using OneDrive personal then this link should help ( my solution was for OneDrive Business)

https://community.powerbi.com/t5/Power-Query/PowerBI-support-for-OneDrive-Personal-as-data-source/td...
Highlighted

SO sorry for the late response... was out of town. Your solution worked! Thank you so much! You saved my excel life  @Wyn Hopkins 

Highlighted
Glad I could help
Related Conversations
Sorting out duplicates.
lesleypohl in Excel on
2 Replies
Excel graphing and trendline bug?
ET_69 in Excel on
0 Replies
Offset
Jrm92 in Excel on
0 Replies
List data validation
Tm81700 in Excel on
1 Replies
Workbooks Need to be locked for editing
JocelynPHenning in Excel on
0 Replies