Issue
I am trying to merge data from multiple Google Sheet queries into one Table widget using a joint query. Specifically, I have three tables (sheets) and I want to assemble an invoice by selecting data from two of the sheets and matching them with the main invoice number from the third sheet. However, joint queries are not supported in Google Sheets, so I will need to use a JS object to merge the data.
Resolution
The solution for using joint query from Google Sheet involves merging data from multiple sheets using a JavaScript Object. This solution requires creating a custom script that extracts data from the relevant sheets and merges them based on a shared column or variable.
To do this, the developer can write code that loops through each row in the customer sheet and compares it with each row in the invoice sheet, looking for matching customer IDs. Once a match is found, the code can then loop through the invoice item sheet and extract all the items associated with the matching invoice ID.
The resulting data can then be stored in an array or JSON object and used to populate a table widget or other visualization. The code example provided in the forum post mentioned above demonstrates how this can be done, and can be adapted to suit other use cases as needed.
Overall, while a joint query function is not available natively in Google Sheets, developers can use JavaScript and other tools to achieve similar results and merge data from multiple sheets in a flexible and customizable way.