Skylar
asked on
How to Import data into google sheets
I have this https://icsc.un.org/resources/sad/dsa/restr/history/History102018.XML link where in Microsoft Excel, I can import its data via the XML and also via power query both way works.
my boss asked me to make this work into Google sheets. I used IMPORTXML but it does not work, perhaps due to the limitation of function.
the total rows are 16422 rows data. I searched a lot in forums and failed to find a solution. I would greatly appreciate your help on this. I can use Scripts or anything that would make this work in Google Sheets.
Excel-File.xlsx
my boss asked me to make this work into Google sheets. I used IMPORTXML but it does not work, perhaps due to the limitation of function.
the total rows are 16422 rows data. I searched a lot in forums and failed to find a solution. I would greatly appreciate your help on this. I can use Scripts or anything that would make this work in Google Sheets.
Excel-File.xlsx
This relates to a previous question asked by same person.
The above XML file link sometimes generates a 200 + sometimes a 500, so best to pull a copy of the file to your local disk... until you get a 200, then follow Sam's instructions.
The above XML file link sometimes generates a 200 + sometimes a 500, so best to pull a copy of the file to your local disk... until you get a 200, then follow Sam's instructions.
ASKER
Thanks for your comments.
The link generates dynamic data it changes regularly and that is what we need. so the values change in that link. so in Excel it works fine, when i refresh it, it will pull the updated data.
If I am to use Excel and then upload it to Google sheet, then it will be a manual work, so everyday i open excel and then refresh and then upload to google sheet?
is there a way to link the google sheet automatically with Excel without having to upload manually?
The link generates dynamic data it changes regularly and that is what we need. so the values change in that link. so in Excel it works fine, when i refresh it, it will pull the updated data.
If I am to use Excel and then upload it to Google sheet, then it will be a manual work, so everyday i open excel and then refresh and then upload to google sheet?
is there a way to link the google sheet automatically with Excel without having to upload manually?
Have you tried the IMPORTXML function directly inside of Google Sheets, rather than manually running it inside of Excel and then uploading to Google?
Google Sheets does support the function:
https://support.google.com/docs/answer/3093342?hl=en
Google Sheets does support the function:
https://support.google.com/docs/answer/3093342?hl=en
ASKER
Hi Lucas,
I did use IMPORTXML directly into google sheet, but it gives error of "resource at url contents exceeded maximum size"
here is the link of my google sheet.
https://docs.google.com/spreadsheets/d/1LaxS7ZYScxqZs-5i9UEgmy_-gwgk5_1gbuA2hFAGi4g/edit?usp=sharing
I did use IMPORTXML directly into google sheet, but it gives error of "resource at url contents exceeded maximum size"
here is the link of my google sheet.
https://docs.google.com/spreadsheets/d/1LaxS7ZYScxqZs-5i9UEgmy_-gwgk5_1gbuA2hFAGi4g/edit?usp=sharing
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
File | Import | Upload from my computer