Retrieve specific or query data from online CSV file in Google Sheet without adding whole CSV data into sheet

user_11786040 0
Viewed 337

I want to Retrieve specific or query data from online CSV file in Google Sheet without adding whole CSV data into sheet. For example, a CSV file is stored in https://raw.githubusercontent.com/LazyGeniusMan/genshin-tc/main/data/characters/anemo/jean/stats.csv, the CSV file content is something like this:

level,ascension,hp,atk,def,ascension_stat,ascension_value
1,0,1143.9840087890625,18.6200008392334,59.83000183105469,Healing Bonus,0
2,0,1238.934618241241,20.16545987895961,64.79588867364873,Healing Bonus,0
3,0,1333.8853640669404,21.71092113836289,69.76178264853479,Healing Bonus,0
...
19,0,2871.399851150665,46.736201928901664,150.1733040253639,Healing Bonus,0
20,0,2967.494634443574,48.30028405925759,155.19903079793949,Healing Bonus,0
20,1,3948.35748844748,64.26408448955544,206.49903003500003,Healing Bonus,0
21,1,4045.5959000870353,65.8467808318328,211.5845682083891,Healing Bonus,0
...
39,1,5808.475131076353,94.54020006523115,303.7825944112774,Healing Bonus,0
40,1,5908.002163144411,96.16014702812208,308.98782670921355,Healing Bonus,0
40,2,6604.931057187379,107.5028460790254,345.437827472153,Healing Bonus,0.055399999022483826
41,2,6703.313642667665,109.10416551193248,350.5832055755236,Healing Bonus,0.055399999022483826

Let's say I want to retrieve 3948.35748844748, which have condition of level=20 and ascension=1. So if possible, I need to get specific cell/column/row based on 1 or more condition, if not I'll try to add new column as identifier (column id).

I tried to use QUERY function but the column name is not detected, it says Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: hp. The formula I used is:

=QUERY(IMPORTDATA(https://raw.githubusercontent.com/LazyGeniusMan/genshin-tc/main/data/characters/anemo/jean/stats.csv), "SELECT `hp` where `level`=20 and `ascension`=1",0)

I can get column name detected by using Col1, Col2, etc as column name, but I need to make column name specific because I have many CSV files that have different column name.

Am I missing something that make column name can not be detected? is there a way to declare CSV first line as column name or something?

note: not sure if this relevant, the CSV file is generated using Node.js

google-sheets-formula google-sheets csv
1 Answers

try:

=QUERY(IMPORTDATA("https://raw.githubusercontent.com/LazyGeniusMan/genshin-tc/main/data/characters/anemo/jean/stats.csv"), 
 "select Col3 where Col1 = 20 and Col2 = 1", 0)

enter image description here

update 1:

try:

=DGET(IMPORTDATA(A5), "hp", {"level", "ascension"; 20, 1})

enter image description here

enter image description here

update 2:

query solution via flipping the table and selecting columns we need to work with. then sorting column headers alphabetically and flipping it again so we would know to point out which column to filter

=QUERY(TRANSPOSE(SORT(QUERY(TRANSPOSE(IMPORTDATA(A5)), 
 "where Col1 matches 'hp|level|ascension'"))), 
 "select Col3,Col2 where Col1 = 1 and Col3 = 20", 0)

enter image description here

user_56326290
Related