17 votes
Accepted

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

Tl;Dr The importhtml(), importxml(), importdata() and importfeed() functions only work with public pages that do not require signing in or other forms of authentication. Further, if the content of a ...
Rubén - Volunteer Moderator -'s user avatar
7 votes

What are the refresh characteristics of the Google Spreadsheet Import functions?

#Short answer You are right, the import functions updates their result at certain intervals. #Explanation From Change a spreadsheet's locale, time zone, recalculation, and language, a help article ...
Rubén - Volunteer Moderator -'s user avatar
6 votes

How to know if Google Sheets IMPORTDATA, IMPORTFEED, IMPORTHTML or IMPORTXML functions are able to get data from a resource hosted on a website?

(maybe not the answer, but here is a quick humble scraping guide) all starts with disabling JavaScript for the website to see what's left and what's possible to be scrapped the first choice is always ...
user0's user avatar
  • 26.5k
5 votes
Accepted

Display imported data in reverse order, so that the most recent entries are on top

This is the easiest way to achieve the descending sort: =sort(importdata("http://www.tr4der.com/download/historical-prices/SPY/"),3,false)
Aurielle Perlmann's user avatar
3 votes
Accepted

IMPORTDATA In Google Spreadsheet and keep the leading zero

As best as I can establish, IMPORTDATA won't respect leading zeros in ANY field containing numbers; the field will always be treated by IMPORTDATA as numeric, regardless of sheet formatting. This ...
Tedinoz's user avatar
  • 5,701
2 votes
Accepted

Only run ImportData when Google Sheet is open

You can use Google Apps Script to Create a menu with an item that runs a script to import your data The script will not run in Android P.S. Also there is the possibility for Triggers, naming a ...
Riël's user avatar
  • 197
2 votes

Google Sheet "across spreadsheets you've created"

Across all the sheets you have created. I have run into this same issue before. this includes test sheets youve played with etc. anytime you have added those import functions and not removed them or ...
Aurielle Perlmann's user avatar
2 votes
Accepted

Trouble using IMPORTDATA when text includes commas

Formula Delimiter: ¿ =IMPORTDATA("https://help.shopify.com/txt/product_taxonomy/en.txt", "¿") Explanation Sheets will by default assume the comma , as delimiter so you are forced ...
Blindspots's user avatar
  • 5,037
2 votes
Accepted

How can I import json from url using only existing formulas of google sheets (without scripts or apps or whatever else additional there is)?

Use importdata() and extract numbers only by removing labels with regexreplace(), like this: =lambda( data, numRows, numColumns, query( makearray( numRows, numColumns, lambda( ...
doubleunary's user avatar
  • 14.4k
1 vote

Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets

Another solution: Place your original IMPORTDATA formula in A1 of some sheet. Hide Col A if you like. Then in the top cell of some other column (say, Col B), you can place the following formula: =...
Erik Tyler's user avatar
  • 5,048
1 vote
Accepted

Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets

Split the data before applying regexextract(), like this: =arrayformula( iferror( regexextract( transpose( split( query( transpose( importdata("...
doubleunary's user avatar
  • 14.4k
1 vote
Accepted

Using IMPORTDATA with a csv file with different numeric format than locale

Just change the locale of the CSV file, like this: =IMPORTDATA("https://cryptorates.ai/files/standard.csv"; ","; "en_US") https://docs.google.com/spreadsheets/d/...
Alan Grainger's user avatar
1 vote

Using IMPORTDATA with a csv file with different numeric format than locale

You can change the locale of your spreadsheet. Click File > Settings. Regarding IMPORTDATA it hasn't any other parameter than the URL. Instead of IMPORTDATA, you might try to use a Google Sheets ...
Rubén - Volunteer Moderator -'s user avatar
1 vote
Accepted

Google Sheets' IMPORTDATA randomly converts number

It looks that Google Sheets sometimes thinks that 2385.1 is a date as 177418 is the serial number for 2385-10-01 00:00:00. You could try changing the spreadsheet locale (File > Settings > Locale)...
Rubén - Volunteer Moderator -'s user avatar
1 vote

IMPORTDATA Google Sheet CSV on Google Drive

The file should be shared to anyone or to anyone with the link If the file is "small enough" the URL to be used should have the form https://drive.google.com/uc?export=download&id=FILE_ID If ...
Rubén - Volunteer Moderator -'s user avatar

Only top scored, non community-wiki answers of a minimum length are eligible