How can I join an sql query to a google sheets

[igoraguiar]

Hi all

I have a db with a payments table. And I also have a simple google sheets with one column with the list of payments that were already notified to the customer. The payments table has a pin column where the codes that match the reference column from notifications google sheets are.

So after running the two queries I am running a Query JSON to SQL like below, but all I get is empty data. What am I doing wrong?

Thanks a lot!

select *
from {{ query1.data }} as payments
join {{ query2.data }} as notifications
on payments.pin = notifications.reference

[mark]

Can you try this:

select *
from {{ formatDataAsArray(query1.data) }} as payments
join {{ formatDataAsArray(query2.data) }} as notifications
on payments.pin = notifications.reference

[igoraguiar]

thanks for the reply Mark.

I get "no rows to display" even though there is one match in my test

[mark]

Can you share a screenshot of the left sidebar with these queries data properties expanded? It seems like the returns from the two queries may be in different formats.

[igoraguiar]

this is query1

this is query2

[mark]

Ah, yes, query 1 is returning an object of arrays, while query2 is returning an array of objects.

I think

select *
from {{ formatDataAsArray(query1.data) }} as payments
join {{ query2.data }} as notifications
on payments.pin = notifications.reference

should work

[igoraguiar]

@mark

select *from {{ formatDataAsArray(query1.data) }} as paymentsjoin {{ query2.data }} as notificationson payments.pin = notifications.reference

it did!!! thank you so much mark!

[mark]

No problem! Glad we could get that sorted out!