0

I want to query a bunch of files kept on my Google Drive with the following folder structure:

\2021\JUN\01JUN
\2021\JUN\02JUN
\2021\JUN\03JUN
\2021\JUN\04JUN
...
\2021\JUL\01JUL
\2021\JUL\02JUL
\2021\JUL\03JUL
\2021\JUL\04JUL
...

Inside each folder is a csv of the following structure:

enter image description here

In each file (Eg: ACC.txt), is the following data:

ACC,20210701,09:08,2021.80,2021.80,2021.80,2021.80,465,0

While the CSV does not have any headers, the headers are Name, Date (YYYYMMDD), Time, Open, High, Low, Close, Vol, 0

I want to find out if it is possible to query these files?

For eg: I pass 3 parameters: ACC, 20210701, 10:28

and I should be able to query the High and Volume data in that file for that Date and Time.

Is this magic possible in Google Sheets?

CuriousDev
  • 1,255
  • 1
  • 21
  • 44
  • Does `ACC.txt` contains only 1 line, or it is only example of one of the lines? – idfurw Aug 05 '21 at 02:17
  • Each text file contains many lines. The Date remains the same but the time changes. Eg: ACC,20210701,09:08,2021.80,2021.80,2021.80,2021.80,465,0 ACC,20210701,09:16,2021.75,2021.80,2011.00,2011.20,5522,0 ACC,20210701,09:17,2013.25,2013.85,2008.25,2013.35,3958,0 – CuriousDev Aug 05 '21 at 02:58
  • Do you prefer a validation on the path with the date input (second parameter), meaning only to look at the `ACC.txt` under `\2021\JUL\01JUL` in your example? – idfurw Aug 05 '21 at 03:21
  • 1
    This is how I get the csv data from my GPS tracks app in my phone into an array in Google Apps Script `var vA=Utilities.parseCsv(file.getBlob().getDataAsString());` after I upload the the file from DropBox. vA is a 2d array designed to enable file loading into a spread sheet with setValues(). Or you can iterate through the array without loading into a spreadsheet. So the question is can you iterate through an array and collect the data that you need. – Cooper Aug 05 '21 at 05:13
  • @idfurw Yes. For eg: if I pass INFY, 20210630, 11.15 I should fetch INFY.txt from \2021\JUN\30JUN folder and then fetch the line containing data at 11.15. – CuriousDev Aug 05 '21 at 17:09
  • @Cooper At present, there are hundreds of files in many folders and sub-folders. I am stuck at figuring out how to traverse these folders and choose the right txt file and finally the correct line based on the parameters I send (Name, Date, Time). – CuriousDev Aug 05 '21 at 17:11
  • I might suggest that you travers through the folders and find the files and then store all of the ids in a spreadsheet and then maybe another function goes through that list and extracts all of the data to other spreadsheets. You can merge the one that are similiar if desired. – Cooper Aug 05 '21 at 17:31
  • Yes, you should better consider storing (managing) the id of the spreadsheets in a index spreadsheet, instead of search through the folders everytime, in case you need to query frequently. – idfurw Aug 05 '21 at 23:30

0 Answers0