Alternative methods to update relate tables besides action ("Request Entity Too Large" error)

Hey folks, i could really use some help here.

Long story short

i have a photo storage app that contains 2 Google Sheet tables
A “LOCALE” spreadsheet - listing unique names of locations
A “FEED” spreadsheet - listing photo descriptions, including the locations
Both of these tables have a “Location” column, which references each other with a REF column.

Problem is this:
I created an action that, when i edit a location name in the “LOCALE” spreadsheet, it automatically updates all the location names in the “FEED” spreadsheet.

It works fine for most items. However, if i try to edit a LOCALE name that has a lot of entries in the FEED spreadsheet (over 300 or so) i receive a “413 - Request Entity Too Large” error from the Google server, and will not update the name. Troubleshooting indicates i cannot simultaneously update numerous entries without encountering this error.

My question is this: Can anyone recommend an alternative method to update multiple entries other than a custom action (or Google formulas)? This feature is a core feature of my application, no not being to do this severely limits it’s usefulness.

Appreciate any help.

0 35 1,740
35 REPLIES 35

The AppSheet REST API allows you to update a collection of records in a single action.

You can use an AppSheet webhook to invoke the AppSheet REST API. Your webhook would need to include a JSON template that creates the JSON payload that specifies which record need to be updated and their data values.

Having said this, I would first ask myself if there is a database design problem that is making such a large update necessary. Whenever an update to one table requires an update to a whole set of records in a second table, I always ask myself if my database design is wrong. This can happen if I am erroneously repeating data in the second table that should instead by obtained via a reference to the first table or some other table.

In your case, the problem may be that the “Locale Name” is repeated in every record in the “FEED” table. Maybe you should introduce a third table called “FeedLocale” that contains an Id field and a Ref to a record in the Locale table. Your “FEED” would then contain a Ref to the record in the “FeedLocale” table that specifies the “Locale” for the entire set of “FEED” records… If you need to change the “Locale” for all of the related "FEED"s you can simply update the “Locale” value in the “FeedLocale” table.

Adding to Phil’s suggestions about your database design, I’d also make sure you know about the difference between key and label columns. For instance, I would include a hidden uniqueid() key column in your LOCALE table, and set another column (locale name) as the label column. Then in your FEED table, the location column would be a ref-type to the LOCALE table, but the user would only see the value of the label column, not the gibberish key. Also that label column would be editable, and that edit would automatically show in all referenced FEED records, whereas key columns are not editable.

Thanks for answering guys.

Apologies if i seem terse, but neither the labels nor the duplication are not the problem.

To briefly explain, my app records metadata of photos our community takes daily. The reason why the “FEED” has a lot of the same entries is because our community often takes photos in similar locations. So the recurring entries are warranted.

Af far as keys and labels, I am using ref columns. The “LOCALE” sheet has a “ID KEY” which the “FEED” sheet is referencing in each record. Updating the name of the ref column in the app works fine.

The problem is that the “FEED” and “LOCATION” sheet also has a “Location Label” column that records that actual location name the ref column is referencing in the spreadsheet. This lets me read the location label of the “Location” Ref column when viewing the spreadsheet (as the the recorded “LOCALE ID” key column is gibberish).

My questions is based on updating the “Location Label” column in the spreadsheet.

All that said

I have been trying for hours to write a simple webhook script that will update the “Location Label” values in the “FEED” Table when i update the corresponding “Location Label” values in the “LOCATIONS” Table.

But i’ve can’t figure it out. The templates I’ve created do not launch, and every modification I make produces a new error.

This is best i could come up with

{
“Action”: “Edit”,
“Rows”: [
<<Start: SELECT(The Feed_Raw[KEY ID (input sheet)], AND(
IN([AREAS COUNTIES KEY ID],The Locale_Numbers[LOCALE KEY ID]),
NOT(IN([Areas & Counties],The Locale_Numbers[Locale Entries]))
))>>
{ “AREAS COUNTIES KEY ID”: " <<[LOCALE KEY ID]>>"
“Areas & Counties”: “<<[Areas & Counties]>>”
},
<>
]
}

The script (I think) says "when a edit happens in the “LOCALE” table, look for all the rows in the “FEED” table. If a there’s a name in the FEED’s “Area” column that is not in the LOCATION’s “Locale Entry” column, update the “Area” name in the FEED table.

I get a the following error

“Webhook HTTP post request failed with exception {“Message”:“REST API invoke request failed: The HTTP Body which should contain the API Action, Properties, and Row data is missing.”} The remote server returned an error: (400) Bad Request.”

If someone could refer to a sample app that demonstrates the webhook feature (or create an example script for me), that would help a great deal. Please just dont refer me to the webhook pages on the Appsheet site. I’ve already checked those out and I’m still confused because the examples are different than the actual webhook interface.

This is because the [locale id] column is set as the key, and ALSO the label for the LOCALE sheet.
Switch the [location label] column in your LOCALE sheet to be the label column (and turn it off for [location id]). Then the [location] column in your FEED sheet should show the readable name, instead of the gibberish key.

2X_c_c80275733e5cfb75163ec89da4d430e0007c56eb.png

thanks for replying @Marc_Dillon.

I may be confused, but i don’t think that’s the case. The label and the keys in the app have always been separate.
Screenshot of the “LOCALE” spreadsheet in the app

When i select the “Locale” entry in the “Feed” form on my app, it records the key, not the label. The Key should not be changed or the app won’t find it, which is why i thought it used the gibberish string. I didn’t want to use the name as the key because i may want to edit it.

Did i get all that right? If not, what do i do?
If so, how can resolve my original problem?

Make sure you include the << End >> expression. I see you’ve got “<>”, maybe you had it there?
Also I believe a start expression that is not in a template file will automatically include commas between records. So I think change “},” to just “}”

Looks like you are missing a comma after “<<[LOCALE KEY ID]>>”

I just realized what i wrote may seem confusing, so let’s do this

2X_7_784708f0eb746aeb35d3923dc31307e51fce78a0.png

This the row from the “LOCATION” spreadsheet. I just made an update to the label name of one of the entries

2X_f_f1c9dcc6364bbb1aaaf3de25f39ac0c1c3392490.png

This is the label name of the entries in the FEED spreadsheet. They did not get updated.
How should I write the webhook script to ensure the label names stay consistent?

this is how the “City of Boston skyline” key is recorded in the FEED spreadsheet. It matches the key from the LOCALE spreadsheet, so i thought it was working as it should. Let me know if i’m wrong

2X_3_338a5f303d1f1fca73078b7a94e6649e68a7ce23.png

Ok that looks setup right. Just had to make sure. So is your goal just to have the location text in the actual feed spreadsheet (as in your looking directly at the sheet, not from the app)?

You can set an app formula in a real column in your feed sheet to [location].[locale entries]. However it will only update when you create or edit a feed record.

Read up about reference actions (running the same action on all child records). This can be triggered by an update workflow on your locale sheet. The action itself would use the dereference formula from my previous paragraph.

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

Yes exactly, @Marc_Dillon I want the label names to appear next to the key.

I sometimes review the database so having the actual names help.

I’ve done the app formula method, “[location].[locale entries]”. That is what led me to my original problem.

There are cases where i have more than 200 entries that need to be changed simultaneously. However, doing this with the dereferences gives me the “Request Entity Too Large” error. I suspect Google will not allow multiple entries to be changes if there are a lot of entries.

This is why I’m asking for help with webhooks. It seems to be an alternative to using actions and formulas in the app, so i should bypass that error.

So you’ve already tried the reference actions like in the sample app I just linked?

If this is something you just need for viewing in the spreadsheet, maybe you can use a vlookup formula in the spreadsheet?

Yep, I’ve tried it. @Marc_Dillon

here’s the action I created to run when i edit a location field

2X_1_19ae48d24abada5a37eb51aa85a7bd21c1055367.png

Here’s is the error

I initially used spreadsheet formulas, but they slowed down the app terribly. I’m using app formulas to keep the spreadsheet formula-free, so it’s as fast as possible.

This is why I’m asking for help with webhooks. The research I’ve done indicates webhooks are the easiest method to update multiple rows simultaneously without slowing down the app or encountering server errors.

Can anyone provide assistance with that?

That’s not the “reference actions” that I’m referring to. Please see the sample app that I posted. That action is fine for the feed records, but then you need another action for the locale records that uses the action type “data: execute an action on a set of rows”.

Yes I can help you with the webhook if nothing else works, but I’m trying to avoid it…heh

I already had that set up. That’s this one.

This is the workflow that triggers it

I’m trying to keep this explanation as short as possible to limit complexity. But to be fully transparent, I have a workflow for the LOCALES that triggers if there’s an entry in there that is not in the “PINPOINT” field (I’m using the PINPOINT sheet to validate the different combinations of locations entries)

The workflow triggers the LOCALE actions above, which then triggers the FEED action in my previous post.

To help clarify things:

  1. This method does in fact work with many entries. There are just a few entries that show up on the FEED spreadsheet alot. I assume actions may not have been designed for that edge case so I want to make sure I have a method to address that.

  2. There is a workaround to making the action just appear as a button in the changed field. If the select that button, it does change the FEED entries, but only one by one, which takes over 10 minutes.

So I hope that makes it clear that this method does work as a last resort, but it’s not ideal.

And trust me, i do NOT want to use the webhook either. I prefer to keep everything strictly on Appsheet. But I’ve already spent over a week setting up and troubleshooting different alternatives. It seems there no other method that will ensure all my entries stay updated without severely affect the app performance. So I hope I can finally get some help on that

t

I understand. Try my above suggestions for the webhook and report back. Another option is to use a google script if we can’t get the webhook to work well.

@Marc_Dillon just saw your latest post. I’ll try it out and get back to you.

I’m using google scripts as well (I’ve been at this for a while now)
Webhooks seems like the lesser evil.


I’ll let you know about the webhook @Marc_Dillon

@Marc_Dillon no dice. Here what i got

These are the errors i get.

2X_e_e453e23556883d6926d5776b273040c13c1fdcf9.png

What am I doing wrong there? It says the API Action, Properties and Rows are missing but i thought the Start string included those??

@Marc_Dillon I’ve since fixed that, still doesn’t work

here’s what i have now

these are my errors

2X_1_140c743e66be5ac7c9feb79525c2c94fc55583c6.png

The errors indicates the instructions to format the webhook are wrong. I’m confident i could resolve the problem myself if i can just figure out what this error is referring to.

Bummer, I was really hoping it was just that

As far as I know, that error is a pretty generic error that gets thrown whenever the payload isn’t quite formatted correctly (no commas between items, mis-matched braces/brackets, missing data).

You can see the actual payload that is getting sent, although with a bunch of forward-slashes that make it hard to read, in the log, like what you posted in the 19th post here. Can you post the most recent workflow log output? I can try to have another look, but I think at this point it might be best to start stripping away complications in the template until you get something that works, then slowly add them back. Like delete with start expression and start with sending a single record.

Another good option is to create the list of record keys in an actual column (move your big SELECT expression to the app formula), then in the template you can more simply call <<START:[new column]>>

Also, AFAIK, you can get rid of most of the properties section, unless you need it. Less text = less chance of error. I’m not sure if you can remove it completely, but I have a working webhook with just:

“Properties”: {
“Locale”: “en-US”,
},

There are two problems with your template.

  1. As Marc noticed, the first is the missing closing quote character following “Locale”: “en-US,
  2. The second is that you are using curly quotes rather than straight quotes.

@Phil @Marc_Dillon
Still not working for me.

This is what i put in as a test. Same error.
I added the straight quotes but i get the same issue.
Is the format still wrong?

    {
        "Action": "Edit",
        "Rows":[
        {
        "Areas & Counties": "Areas & Counties"
        }
        ]

I just tried your template file again. It has the same two errors.

  1. You are still missing the quote following “US” in the Locale line:
    "Locale”: “en-US,

  2. All of the names and values in Properties have curly quotes.

Here is the template file contents.

{
“Action”: “Edit”,
“Properties”: {
“Locale”: “en-US,
“Location”: “42.361145, -71.057083”,
“Timezone”: “Eastern Standard Time”,
“UserSettings”: {
“Option 1”: “value1”,
“Option 2”: “value2”
}
},
“Rows”: [
<<Start: SELECT(The Feed_Raw[KEY ID (input sheet)], AND(
IN([AREAS COUNTIES KEY ID],The Locale_Numbers[LOCALE KEY ID]),
NOT(IN([Areas & Counties],The Locale_Numbers[Locale Entries]))
))>>
{ “Areas & Counties”: “<<LOOKUP([AREAS COUNTIES KEY ID], “The Locale_Numbers”, “LOCALE KEY ID”, “Locale Entries”)>>”
},
<>
]
}

You are still missing the quote following “US” in the Locale line:
"Locale”: “en-US,

All of the names and values in Properties have curly quotes.

@Phil

That’s odd. I typed the code directly into the body. I think the problem was i still had the body template file listed there, it must have been using that instead of the body code. I’ve since removed it

Code still gives me a slightly different error. I’m assuming the properties section is mandatory

Yes you must have Properties.

If you have both an inline body and a body template, the body template is used.
Your body template seemed fine aside from the two problems discussed earlier.

I will be releasing a change on Friday afternoon Seattle time that verifies that the webhook JSON body template can be parsed at webhook body creation time. If not, it will display a more descriptive error message.

@Phil
@Marc_Dillon

Thanks Phil.

I made the changes and looks like i’m making a progress.

This is what i have now

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "42.361145, -71.057083",
"Timezone": "Eastern Standard Time",
"UserSettings": {
"Option 1": "value1",
"Option 2": "value2"
}
},
"Rows": [
<<Start: 
SELECT(The Feed_Raw[KEY ID (input sheet)], AND(
IN([AREAS COUNTIES KEY ID],The Locale_Numbers[LOCALE KEY ID]),
NOT(IN([Areas & Counties],The Locale_Numbers[Locale Entries]))
))
>>
{ "Areas & Counties": "<<LOOKUP([AREAS COUNTIES KEY ID], "The Locale_Numbers", "LOCALE KEY ID", "Locale Entries")>>"
},
<<End>>
]
}  

What i want is for the embedded LOOKUP formula to add data in all the rows of the “Area & Counties” column (in the “Feed_Raw” spreadsheet)that are found WITH the SELECT formula i added. the select formulas work fine in Appsheet. But using the webhook, i get the following.

I assume the webhook is still formatted wrong somehow. Can anyone tell me what I’m doing wrong here? How can i get the update to be updated for just the rows found under the select condition?

I believe your webhook is now formatted properly since you’re not getting the “http body … is missing” error.
This new error seems to be saying that you haven’t supplied a row key value with which to select a row to edit. This appears to be correct, as you are only sending out one column value (“Area & Counties”), which I don’t believe is the key column for your data.

yes, you are correct @Marc_Dillon

But i thought this part of the rule selects the row to edit the “Area & Column” cell in

    <<Start: 
    SELECT(The Feed_Raw[KEY ID (input sheet)], AND(
    IN([AREAS COUNTIES KEY ID],The Locale_Numbers[LOCALE KEY ID]),
    NOT(IN([Areas & Counties],The Locale_Numbers[Locale Entries]))
    ))
    >>

Using in Appsheet gives me the key value for the row i wish to edit. So how do i use this formula to select the right rows to use this expression?

A Start expression accepts a list of row references, and iterates upon them.
An Appsheet API webhook, with an “Edit” action, requires you to send the row key value, as well as the columns and values you wish to edit. You need to edit this line to include a key-value pair for the row key:

Such as:

{ “Areas & Counties”: “<<LOOKUP([AREAS COUNTIES KEY ID], “The Locale_Numbers”, “LOCALE KEY ID”, “Locale Entries”)>>” , “KEY ID (input sheet)” : [KEY ID (input sheet)] },

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "42.361145, -71.057083",
"Timezone": "Eastern Standard Time",
"UserSettings": {
"Option 1": "value1",
"Option 2": "value2"
},
},
"Rows": [
<<Start: 
SELECT(The Feed_Raw[KEY ID (input sheet)], AND(
IN([AREAS COUNTIES KEY ID],The Locale_Numbers[LOCALE KEY ID]),
NOT(IN([Areas & Counties],The Locale_Numbers[Locale Entries]))
))
>>
{ "Areas & Counties": "<<LOOKUP([AREAS COUNTIES KEY ID], "The Locale_Numbers", "LOCALE KEY ID", "Locale Entries")>>",
"KEY ID (input sheet)": <<[KEY ID (input sheet)]>>
}
<<End>>
]
}

adding that in gave me the first error again


what am doing wrong here

Quotes around this?

@Marc_Dillon missed that.

Good news is the webhook is now working like it’s supposed to.

Bad news is that it now gives me the error I got with actions which prompted me to go to webhooks in the first place (after 5+ minutes of synching), “Request entity too large”

I suspect it won’t work if there are too many results to change.

That said, I’ve given up on webhooks, and decided to use google appscripts instead. It seems this needs to be handled on the Google side.

Hate to ask, but can anyone assist me with that? I simply need a way to write to a cell if another cell equals a particular value. I can share the code I’ve written in Google script so far.

Wow, that sucks.

Yes I can help with a script. Probably best if you email me at marcdillon@gmail.com

Top Labels in this Space