Display data from two Google Sheets in one table

tomm · February 21, 2023, 3:59pm

I have searched all over for an answer to this, so if it has already been asked, I apologize. I have a spreadsheet that I inherited that is used to track the number the number of foam shipping blocks that are used when a shipment goes out and the number returned with the goal being to find who is not returning the blocks.

There are two separate tabs in the spreadsheet, one for 2022 and one for 2023. I have been able to add all the blocks for 2022 and all the blocks for 2023, but to get an accurate I need to be able to add those two numbers together. I tried using JOIN and that wasn't returning the correct numbers.

Any help would be most appreciated. Let me know if I need to provide more information.

stefancvrkotic · February 22, 2023, 2:00pm

Hey Tom!

Assuming you have 2 queries pulling data from two separate sheets like this:

You can simply .concat() those 2 arrays into one array and analyze the data as 1 set.
This is an example of transformer but you can directly load this data into a table or filter/manipulate values of combined array to display them in other elements.

tomm · February 22, 2023, 5:35pm

Thanks for the information, however, I'm a little confused as to how I use the transformer. I am using two queries pulling data from two separate sheets, but I'm not sure how to implement the transformer.

Tess · February 22, 2023, 6:19pm

Hi @tomm you can add a standalone transformer from here:

And then you can reference any queries or components inside {{}}

tomm · February 22, 2023, 6:48pm

HI @Tess,

I set up the transformer and it ran just fine, but now, if there is a record from both 2022 and 2023, they are displayed in two different rows in the table. Plus, I'm not sure how to add the 2022 and 2023 numbers together to get a total. I tried running a query against the transformer data to pull UNIQUE dealer and it seems to only be pulling that information from the 2023 table.

stefancvrkotic · February 23, 2023, 8:46am

If you add a resource query "Query JSON with SQL" you can easily sum and pull-out unique dealers from the combined table.

I'll provide an example where you would count total items per unique dealer. Then on per need you can group by date, month and other columns depending on your needs.

tomm · February 23, 2023, 1:13pm

Thank you @stefancvrkotic. I was able to get the data I needed to be displayed in the table.

ttriplett76 · February 26, 2023, 3:10am

The person who's shipping the blocks also you may want to check on the tracking numbers and who is responsible for tracking the blocks get the I.P addresses for both and check the people's logs for any interference with your shipping from there company get back to me .