App Script push to glide table

Ask for Help
1

Hi,

I have built a google app script that adds new leads every 5 minutes to a google sheet.

I want to push these to an existing table in glide

What is best practice integration in terms avoiding duplicate data to be pushed:

  1. Should I check the glide table before I send new ones ie via unique identifyer,
  2. or should i delete gsheet rows once they have been sent to glide?
  3. or is there a better approach?

Im new to google app script so a descriptive response appreciated :slight_smile:

2

Is your script the only way that new leads will be added?
If yes, I would check for duplicates before sending. Load the entire sheet into a JSON Object in memory, iterate through it and remove any duplicates, and then send the remaining new rows to Glide.

But if new leads can be added in other ways (eg. by users), then that method won’t be 100% foolproof.
If that’s the case, then you’d probably need to implement some sort of duplicate prevention in your Glide App as well - eg. by using a Custom Form.

3

Thanks, that makes sense.

The previous solution: lead enquiry email is received, zapier pics it up, parses it and send it to my glide table - this was nice and realtime, but costly

Now, im trying to replace the above with a google appscript solution. My thinking is:

  • run script every 5 minutes to poll for new lead enquiry emails, if yes, add them to the spreadsheet. (I remove labels on gmail as soon as its processed, so i wont have duplicates coming from mail)
  • im thinking to add a function to push all new rows into glide in the same script… but im scared i loose track and wont be able to troubleshoot… perhaps i should
  1. push it the same time to glide
  2. Delete from spreadsheet
  3. But add with unique identifyer to a “log sheet” that way i can see when new leads were pushed to glide

What do you think?

4

Your spreadsheet is connected to Glide, right?

Assuming yes, then don’t add new rows to the spreadsheet. Just add them to Glide via the API and then let them sync back to the spreadsheet. Otherwise things will get horribly messy.

The API call will return a list of RowIDs for the newly created rows, so if you want to keep some sort of log you can capture those and write them to another sheet somewhere.

1 Like
5

Is your existing data in Glide Tables or Google Sheets?

If you want to check duplicate data against a Glide Table, you would need a Business/Enterprise team to be able to use the query function.

6

Hi both, thanks for the update, a few questions

@Darren_Murphy ,

  1. my spreadsheet is not currently connected to the glide app - but I can probably just need to check as the New solution is on a different gmail account than the glide app one… not sure if it will impact on either side of the solution yet.
  2. what do you mean sync them back to the spreadsheet? do you mean, once the api pushes them they will anyway live on the spreadsheet as its connected as a table on the app?
    that might be helpful, can i assume ill get 5 new rowId’s if the api call pushed 5 in the last batch or does it not work like that?

Here is a diagram on my current situation - trying to figure out what hte best way for the green line is

Screenshot 2023-10-19 at 10.48.21
Screenshot 2023-10-19 at 10.48.211980Ă—970 56.2 KB

7

Thanks ThinhDinh, we are currently on Pro only

8

Oh, I’m sorry. I completely misunderstood. I assumed that the source of your Glide Table was the Google Sheet. But I see now that’s not the case at all.

Okay, so disregard my earlier advice. Nothing will sync automatically.

So if it was me I’d be asking myself why do I need to maintain the same data in two different places, and start looking for ways to drop the Google Sheet and just keep everything in Glide. If that’s the way you want to go and need some ideas for managing duplicates in a Glide Native table when you don’t have access to the query API, then I might have an idea I can share.

1 Like
9

Thanks Darren, thats exactly what i want to do, use app script to parse the email data but send it directly to the glide table so that i can switch off zapier

Im open for any ideas

10

Okay, so here is what I have in mind.

I’m making the following assumptions:

  • Leads can be added via the API, and they can also be added by Users via the App.
  • The first lead added will always be considered the “original”
  • Your Users will always be signed in when using the App.
  • Rows in your Leads table will never be reordered once added. (That’s not easy to do in a native table, and certainly not something that could be done by accident. So hopefully that’s a fair assumption).

The general idea is to first decide how you define a duplicate, then create some logic to detect duplicates, then hide them from view and automatically delete them.

Using the classic Glide “Things” table as an example, lets say we decide a duplicate is a record that has the same combination of Name and Category. Using that example, the logic to detect the duplicates would be as follows:

  • Add a RowID column to the table
  • Add a Template column that joins the Name & Category
  • Add a multi relation that matches the template column with itself
  • Add a Single Value column that takes the first RowID from the multi relation
  • Add an if-then-else column:
    – If Single Value RowID is not RowID, then User Profile → RowID (more about this below)

The effect of the above is that any row that is detected as a duplicate will have the signed in Users RowID in the if-then-else column. Here is how it looks:

CleanShot 2023-10-20 at 14.33.41@2x
CleanShot 2023-10-20 at 14.33.41@2x2318Ă—696 164 KB

Note that the last 3 rows in the above have been detected as duplicates.

To hide the duplicates from view, all we need to do is use that last column as a filter. That is, only show rows where it is empty.

And to automatically remove duplicates, what we can do is add a multi relation column to our User Profiles table that matches the RowID with the final if-then-else column in the above table. And then use a Delete Rows action somewhere that deletes all rows via that relation. An obvious place to use that is as an onSubmit action when new rows are added to the Things table.

The effect of all the above is that if duplicate rows are added via the API, they will immediately be detected and hidden from view. And the next time a user adds a row (any row) to the table, any and all duplicates will be deleted.

2 Likes
11

Maybe based on this, we can add a query that detects all rows that are flagged as duplicates. Then a button somewhere to delete all of them at once.

1 Like
12

Yeah, a Query that filters where First RowID is not RowID could replace the if-then-else column :+1:

1 Like