I am using the consolidate by folder connection with sheetgo. Everyday new report files are automatically added to a folder, where the sheetgo connection has been set up to consolidated.
The problem I have is that all the column references for the data are different from sheet to sheet. As a result I cannot do a simple query for SELECT A,B,C, because the columns are different in each sheet.
Is there a way to do a query based on the header title? E.g. I am looking to return the results for the columns titled ‘Net Sales’, ‘Total Number of People’, ‘Total number of orders’ etc.
I can use the following sql query in google sheets, but it does not appear to work under the filter option when created the sheetgo connection
=query(‘Sheet Name’!1:1000,“select (”&SUBSTITUTE(ADDRESS(1,MATCH(“Net Sales”,‘Sheet Name’!1:1,0),4),“1”,"")&"),("&SUBSTITUTE(ADDRESS(1,MATCH(“Total Payments”,‘Sheet Name’!1:1,0),4),“1”,"")&") ")
How should I enter the equivalent query into the sheetgo connection?
Thanks for your help