Advanced SQL Query for Consolidating Sheets


#1

Hello,

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


#2

Hey @tokyobird,

This is a great idea!

Let me flag this is as a feature request.

I don’t think there is a way to achieve this as of now.

But let’s think of a workaround:

https://drive.google.com/open?id=1-y7cx29SMlVQGQYbuanbjQsv9u5klRAt

Can you take a look at the solution inside the folder and let me know if that works for you?

Thanks! :smile:


#3

@Bobby thats what i’d do too.
Its an extra connection, but it works. :blush:


#4

Hey @Bobby, thanks for getting back to me.

So the workaround you’ve made reorders the columns from source 2 to match source 1, so that when the consolidate connection pulls data from both sheets, they are in the same order on the consolidated sheet.

This would mean I would have to reorder sheets daily into the same order with the extra sheet, then run the connection. I don’t this work around would work given the amount of sheets that come in daily.

The problem is that these sheets are placed into google drive daily via zapier. The sheetgo consolidation then joins all files in that folder. So if there was any kind of sorting/querying going on, it would have to be automated in the first sheet somehow. I’m looking for automation where minimal effort and data manipulation is required.

The Query Langauge Reference from google says very clearly that queries can be performed using the column identifiers.

e.g.
select dept, salary

I think being able to do this type of query in sheetgo would be a big feature/function. It would allow users to only query specific columns, hopefully for faster operations. It would also enhance the consolidation abilities in my case, where column positions vary from sheet to sheet and so simple queries based on column letter is not accurate. I really hope you guys can put this into motion, because at the moment it has put a big dent in this workflow I’m creating because the figures i’m consolidating are not the correct ones.

Please let me know if you think there are any other work arounds?

Thanks


#5

Hmm I see. Your column structure is changing on a daily basis?


#6

The columns will shift 1-5 positions depending on the day. For example if there are payments from amex for that day, it will add an extra column at CN. If there are no amex payments that day, then column CN will be something else. As a result, the results will be different if you SELECT CN.

The google developers page you reference for the Query languages, says you can select column identifiers which are the items in the first row. https://developers.google.com/chart/interactive/docs/querylanguage#Language_Syntax

As there anything we can do?


#7

Cheers! Let me feed that to our devs.

@rafael do you have any additional input here? Something we may be able to add to the product?

Basically, his challenge is a changing column structure. Makes it difficult to consolidate. It would help him if instead of selecting columns alphabetically using a query, we could select them by their column header names.