Best approach to use data from Glide table in Google Data Studio report

[schrutefarms]

Hi everyone,

Let’s see if I can explain my use case clearly enough. I already use a custom webhook and Make (Integromat) to add data to an external spreadhsset when rows are added to my Sales Order table. I then use this data for some reports in Google Data Studio, showing total sum, date, etc. However, I would like to also create some reports based on the data in the related Line Items table. Since this table is updated very frequently during the process of creating a new sales order (using a custom form), instead of adding/deleting rows to a external spreadsheet every time a line item is added/deleted I would like to find a better approach.

Perhaps I could sync the data in the Line Items table once/day only to an external sheet and then base my report on this sheet. After 24 hours the sheet will be updated again to mirror the current data in the Glide table? How would I do this?

Is there a way to hold this data in Make instead of having to use a separate spreadsheet for it?

Or maybe I should change my approach entirely? My current problem is the frequent updates of line items while creating a new sales order. For example, the user might add/delete rows several times while creating the order. At the moment, it is not until the final step of the custom form that the sales order New Row is created and the related line items are added to it. They also have the choice to delete the sales order and all related line items and start over. This means that I can not accurately use the “Add row” action on the line item table together with a custom webhook to get a accurate report in Google Data Studio, since these line items might be deleted before the sales order is confirmed.

I have been thinking of a way to add a “Confirmed” status or something to the line item rows when the Sales order is confirmed, but have not found a way to update several related items at the same time.

Probably the most confusing post ever, but if anyone understands and has a solution I would be super grateful

[Darren_Murphy]

@schrutefarms

Perhaps I could sync the data in the Line Items table once/day only to an external sheet and then base my report on this sheet. After 24 hours the sheet will be updated again to mirror the current data in the Glide table? How would I do this?

Which Glide Plan do you have?
If you have Business or Enterprise, then you have access to the Get Rows API end point. You could use this to periodically fetch all rows.

@schrutefarms

Is there a way to hold this data in Make instead of having to use a separate spreadsheet for it?

Yes, you could use a Make Data Store.

@schrutefarms

I have been thinking of a way to add a “Confirmed” status or something to the line item rows when the Sales order is confirmed, but have not found a way to update several related items at the same time.

I think the best way to do this is to create a Joined List column containing all the RowID’s of those rows to be updated, and send that via a webhook to Make. And then use Make to update the rows via the API.

[schrutefarms]

@Darren_Murphy

Which Glide Plan do you have?
If you have Business or Enterprise, then you have access to the Get Rows API end point. You could use this to periodically fetch all rows.

Ah, that’s too bad. I am on the Pro Plan.

@Darren_Murphy

Yes, you could use a Make Data Store.

This is great, thanks for pointing me in this direction.

@Darren_Murphy

I think the best way to do this is to create a Joined List column containing all the RowID’s of those rows to be updated, and send that via a webhook to Make. And then use Make to update the rows via the API.

Not a bad idea at all, but I would like to try and keep everything except the custom reports inside Glide and not rely on Make to update my Glide tables. I hope we can get the option to update multiple related rows soon.

[ThinhDinh]

If you’re extensively using Google Sheets for data reporting, maybe you can consider moving the data to Google Sheets instead of using Glide Tables?

It sounds crazy I even suggest that, since I’m always pro-Glide Tables, but consider you don’t have access to the query API, I think keeping the data all in one place might be better for you, than trying to sync them.

[schrutefarms]

That has crossed my mind too. I might need to change the Line items table over to Google Sheet to make this work. But I don’t really like that idea either…