Column not syncing to Google Sheet

I’m missing the content of a couple of cells in my Google Sheet:


The cells are empty. But in the Glide table (mirroring the Google Sheet) they contain data:


But there is a detail: The values in the Glide table are set during an submit activity of type “Set column values”:


I think I remember this working before. What could be the problem?

It’s affecting other columns, too, which get set during the activity. Only columns get synced whose value are entered directly in a form.

Don’t use onSubmit with a form to set column values. Instead, pass the values with the form.

Hm… I don’t understand.

Here’s a simplified example:

  • A user enters into form field A the value 1. The form field maps to column A in a Google Sheet.
  • A math field M increments the value entered in A. M does not appear in the Google Sheet.
  • The calculated value in M also needs to be in the Google Sheet in column B. I need to see the 2 in the Google Sheet.

Hence I use a “Set col value” activity to copy M to B.

How else would I be able to get a calculated field to the Google Sheet?

And as I said: it worked for a long time - and now stopped working. To me it seemed a valid, even clever approach😉

Your experience is a common one :slight_smile:

The issue is that every now and then the Set Column Values will fire before the new row has been created, and so there is nothing to update. And it fails. It might work 99% of the time, but you can almost guarantee that it will fail every now and then. And for that reason, it’s best to avoid it altogether.

For your specific example, where you need to write a value that’s computed from one or more of the form inputs, the best approach is to use a Custom Form. With a Custom Form, your inputs are first written to User Specific Columns, which means you can use them to calculate any additional values that are needed, and then use them all together with an Add Row action.

It’s more work to setup, but it will be much more reliable.

A 99% solution unfortunately won’t cut it for me :slightly_smiling_face: So I’ll try the custom form. Thanks for the illumination of these Glide quirks!

1 Like

Sorry, have to ask one more question before I set out to rework the whole data entry:

For what should I use a custom screen?

I have a table called “Activities”. I want to add and edit data in that table.

Should I create a custom screen and then bind it to “Activities”, put a collection on it and then work with the default screens I can open with actions like “Show form screen”, “Show edit screen”?

I guess that’s what I have already done.

Or can I just create a custom screen to enter data and get that opened when I click the add button or edit menu item of a collection?

I guess I’m confused by the terms “custom screen” and “form screen” offered by Glide’s ±button.

And this does not seem to offer an action to open just any custom form for editing a data record.

And where are the “user specific columns” located? On the user record? Or a record of the “Activities” table?

You see, I am confused - even though this seems such a basic matter.:flushed::roll_eyes::tired_face:

1 Like

Thanks for the link. I remember having seen that app before. But now it seems to have more relevance :wink:

Still, though, I’m struggling with understanding it. It’s an application of a certain technique - but it does not really explain what’s going on/why that’s working. But let me try to reverse engineer and abstract:

There is a table I really want to add data to, eg “Tasks”. Each task has two columns, eg “Subject” and “Due date”.

I create a screen with a collection to show all tasks. So far so normal. Correct?

But here comes the trick: I don’t use this collection’s record edit forms!

Instead I wire the add action to a custom action. (And the edit action as well. But I’ll focus on the add action.)

Now, in this custom action I open a form on another (!) table.:flushed::face_with_monocle: Let’s call that “Tasks_dataentry”. It’s a special data entry table which always contains 1 record with all fields set to “user specific”.

“Tasks_dataentry” can contain the same columns as it’s “base” table “Tasks”. Or maybe not. In any case, whatever columns it contains and whatever data is entered in its forms in the end some (or all) of the “Tasks_dataentry” values are transferred (!) to a record in the “base” table “Tasks”.

The beauty of that:

  • It works more reliably.
  • It can keep the “primary” tables like “Tasks” clean. No (or less) intermediate columns to just calculate some value.

Is that a correct description of the approach? Anything missing? (Sure, some housekeeping is necessary to eg clean values in “Tasks_dataentry” before opening its form for a new record or set values before editing a record.

The “dataentry table” with its forms is like a user specific scratchpad.

What do you think?

Yeah, that’s more or less it. One key point is that you build your Custom Form on a details screen, not on a form screen.

A good way to set it up is to create a Single Value → First → Whole Row column in your User Profiles table that points to your single row Helper Table. Then anytime you need to open your Custom Form (from anywhere in your App), you can use a Show Details Screen via that Single Value column.

That concept App is really old and is built using the old Classic Apps. On my todo list is to create a more up to date version and make it available as a template. I’ll get around to it… eventually :wink:

If it’s just 1 form (details screen) how to distinguish between adding a row and editing a row? Add a “mode” column in the data entry helper table?

An updated concept app would be great - but even simpler than the current one. And with some sort of documentation :wink: I’m happy to help with that.

Yes, that’s exactly how I’ve done it. When navigating to a custom form, you can set up a custom action that first sets the mode through the single value row in the user profile. Then navigate to the detail screen for that custom form.

So if you plan to add a row, you could have a button with a custom action. First a Set Column action to clear all user specific columns, as well as set the mode column to ‘Add’ through the Single Value row. Follow that with a Show Detail Screen action.

If you want to Edit a row it would be similar, but slightly different. First a Set Column action that refills all user specific columns with values from the row being edited, as well as set the mode to ‘Edit’ through the single value row. Follow that with a Show Detail Screen action.

When you use a custom form for both adding and editing, then you will need a custom action on the submit button. First you need to check the mode value. If it’s Add, then you can use an Add Row action. If it’s an Edit, then you will be using a Set Column action, but for that to work, you will also need a user specific column in your work table to hold the Row ID of the row being edited, and a single relation that uses that Row ID to link to the row. This is so you can update the row being edited through that single relation.


Thanks for the detailed explanation.

Before I really invest into rebuilding my app one more question: Is the unreliability a matter of syncing with Google Sheets? Because if so, couldn’t I stay with a simpler solution and do the syncing myself. It does not need to be instantaneous. If anything I only need one-way sync and could do that manually with a scenario once in a while.

Glide’s benefit is supposed to be in ease of use, a simple app model. I fear I’m losing that now with this somewhat convoluted workaround. And does the need for workarounds stop there?:flushed:

I don’t think I’m asking for much: setting values when I ask for setting them. And that just works in 99% of the cases?:face_with_monocle:

I like much about Glide - but this seems to be a case of LOLA: Law of Leaky Abstraction. The abstraction works in principle and for simple things - but for something serious it’s brittle and prone to not hold leaving me with the sudden need for workarounds😕

Or am I too pessimistic?

It’s nothing to do with Google Sheets.
If you’re baulking at the idea of building a Custom Form, then another alternative could be to stick with your current approach, but insert a short wait condition before the set column values action. I’ve never used this approach myself - and I don’t like it - but I don’t see why it wouldn’t work in your case.

Personally, I don’t view Custom Forms as a hacky workaround, but rather another tool in my Glide Toolbox. I don’t use them all the time, but I know that I have them available when I need a little more than is provided by the native forms.

For what it’s worth, Jeff’s description of how he multi-purposes a Custom Form for both add and edit is exactly how I do it - to a word.

1 Like