Hi friends! I don’t know if this a bug or a limitation but I try to understand why Glide doesn’t allow to write array column’s values created by a Lookup (multiple relation) to any Google sheet directly? I could create a dynamic list of selected items by a user using relations, templates and put all things together in an array column via Lookup as you can see in my screenshot (the user chose 5 items of 10 possible). [image] An array column with multiple values can’t be used with a Template, Math or IF-Then-Else tool and this is understandable but why can’t it be written to GS using a Form’s column? [image] I just to need to write these values to GS and later I might modify it, change its format or something else. I don’t see any problems with it … or am I wrong? Any idea is welcome and a little modification inside Glide as well. Thanks y feliz día a todos!

You can try out Join Texts on our staging instance. [2020-08-01 10.04.51] It works very much like a Rollup, but you can also specify a separator. If you use just a newline as the separator you won’t see it in the Data Editor, but it’ll be there if you save the column to a sheet. Writing the array column to the spreadsheet is not just a “restriction” we can remove. Google Sheets don’t support arrays, so we’d have to decide how to write them. For example, how to separate individual values, which Join Texts lets you do :wink:

Because probably Glide is not sure how we want that to be recorded in the Sheet. Do we want to combine them into one cell, or multiple cells from adjacent columns.

I just want it into one cell, it’s so simple! I don’t care the empty fields, only the fields with data as a list. I mean, if the lookup found: item 1 item 4 item 7 I’d like to have into a cell something like: item 1 item 4 item 7 With this I will make a party sure!! :tada: Thanks @ThinhDinh

For reference: [image] Lookups from multiple realations @gvalero like @ThinhDinh mentioned in your other thread, there are probably several ways to approach that as far as how it should be treated. Do you save it as a comma delimited list (or some other type of delimiter)? Do you save it in an array of columns? Do you save it as a value per row? I have my own uses for what you are asking for as well, but at the same time as a programmer myself, I know that arrays are a completely different monster compared to single strings. I would hope that Gl…

Hi again @Jeff_Hager Here I put a GIF to show you what I try to get (I know you hate GIFs but a set it to almost slow motion :smiley:) The steps are (sorry for Ping Pong effect that sometimes appears, it is like a plague) : You choose the restaurant (Firenze) See its menu to select dishes Check mark the dish and quantity to ask for and with every clic on dish name, its quantity +1 (if you want to change quantity due to error, just uncheck the dish and start over) While you are selecting dishes and their quantities, the APP shows the total order amount in a green box at bottom You can see your list of dishes and quantities in the Inline list I put bellow as well (horizontal orientation) [array-gif] Up to here, everything is normal but using Data Editor you can see: The Restaurant name colum The selected Quantity column The Dish name column A Template column to join Qty+DishName (TP_CantyPlatoPedido) The famous Lookup column where I put all dishes selected by user but using only my Template column indicated aboved. Here I have my dynamic list (3 products of 10 possible) ready to send the order and write to GS but, Glide doesn’t allow use a lookup column if this looks like an array with multiple values. It doesn´t make sense! My Lookup column is an array unidimensional (LK_ListaPlatosPedidos) and could be written to any cell in my GS without problem. With regard to the format and delimiters, I understand the problem but I can live with a simple list separated by a comma, slash or whatever that Glide wants to use (I can configure my Template to separate/split items). If I copy the Lookup column value from Data Editor manually and past it into a cell, I get a wonderful result as my picture shows…(no spaces, no empty data, etc… only the info/values found by my lookup as a list). It’s what I want!. [image] If you have a plan C to get it I will appreciate it although I think that a little modification inside Glide could fix it forever. Thanks again for your help and time Te cuidas, seguimos mañana! :slightly_smiling_face:

To @Wiz.Wazeer and @ThinhDinh I could read your posts and the little discussion about the script used in your APP and I understand both points of view. but I think Wiz was refering to use scripts in general to these cases and not to your specific script but anyway, l’m not going to re-open that misunderstanding. The scripts have these problem with GS when a spreedsheet has many movements or changes and in my opinion, GS loses the control of events queue therefore, the triggers are not fired always. I have seen and dealt with these issues before in other real-time systems including the annoying ping pong effect! If Glide can modify a little (or eliminate) this restriction and let any lookup (multiple relation) can be written to one cell in a GS, we will save much effort, scripts, columns and the life will be easier. [image] And now with the new Sheet Edits quota (1000 per month) on Free apps, the classic procedure used by custom Shopping Carts where each item/product is written to GS using one (1) new row now, it wil consume 2 “edits” and any APP will have problems with its capacity and funcionality very fast. It’s not my case because my APP is a Pro but I’m thinking about new users and their no complex APPs. This improvement or change associated to lookup (multiple relation) can help to many people with medium/big APPs. May @Mark give us a hand with it in order to have a final solution or hope? Sorry if I bother you my virtual friend. Thanks eveyone for your help! Feliz día para todos.

@gvalero What is it that you need to with the array in the Google Sheet? What’s missing in Glide that requires that to be in the spreadsheet?

Mark, the problem we have been facing is a very simple one. We are not able to transfer multiple items created using a multiple relation to a new sheet via a lookup. Below is an example: Menu Sheet Customer X selects following items from sheet “Menu”: Item 1 (on row 1) Item 2 (on row 2) Item 3 (on row 3) Orders Sheet All 3 items get passed to sheet “Order” in the following order: Customer X (fetched into sheet “Order” either using Lookup or Column) Item 1 (on row 1) Customer X (fetched into sheet “Order” either using Lookup or Column)Item 2 (on row 2) Customer X (fetched into sheet “Order” either using Lookup or Column)Item 3 (on row 3) Sheet “Confirmation” This is where it all begins to get complicated. Let me take you through the process item by item: The normal process/workarounds When X confirms order, the Confirmation sheet is created but with only 1 item (not all 3, and we will come to why?). If that is so, then how was I able to show all items on the “Confirmation” sheet (without using scripts). This was just a trick many before me used and we all still sometimes over use. We are able to perform this trick by pulling the “Orders” sheet as an inline list, displaying only the items ordered by tying the items to X. All good and well! So what’s the problem>? The Problem The problem: If we set the “Confirmation” sheet to edit in- app, and we have to to save rows, what gets deleted is not what’s on the Confirmation" sheet but "what’s on the Orders sheet. When this happens, all we are left with is the Qty and total price, email of X etc. Poor chef does not anymore know what he needs to prepare! Solutions: A. Google scripts - my opposition to scripts is not a personal one but a common sense one. We are past the age of google scripts. Our competitors are growing by the day, if not hour, making all sorts of wild claims. Give us the tools mentioned in B below, and we will take the competition to the so called competitors. B. Glide script: currently, you can paste any single row to any row on any sheet you like via a lookup, provided a single relation has been created between the shits in question Give us the ability to do lookup of multiple items by row/column, whether created by single or multiple relations. This would give us the ability to pass all 3 items (in our example) to the Confirmation sheet. Thank you

Hola @Mark, thanks for replying My request is simple, as you know, a lookup is an array column with multiple values. Any lookup using a simple relation can be only used with a Template, Math, IF-Then-Else tool or into a single value component, this is understandable but what if the lookup uses a multiple relation and its result generates a kind of list (like a unidimensional array column)? I’d like to write that list (lookup with multiple relation) to one cell in my GS in order to have that info and handle it later. Something like my picture shows bellow: [image] To get it, the only way is using a Form and associate my lookup colum to one cell of my spreadsheet by choosing a column component but currently, Glide does not allow it, only lookup with a unique value (single relation) can make it. Why? I don’t know :upside_down_face: [image] If Glide can generate dynamic lists using multiple relations why we can’t use these lists (working as an array column) and save it to GS? . On APPs like Delivery where the user can select N items from a list, this improvement will save a lot of work and data without creating more columns, templates, math operations, etc. I hope it can help you to understand this problem which has a simple solution: allow the Form’s Column component to use a lookup (single or multiple relation) and write its data to one cell in any spreadsheet. Thanks again for your help. Feliz domingo!

This makes me wonder if crossing the bridge between Glide Editor and GS is a difficulty for non-image Array columns in general. My challenge, for weeks, has been going in the opposite direction: Multiple TEXT columns in GS (heading 1, heading 2, heading 3) can’t be automatically merged as an Array column in the editor and therefore cannot be used as options in a choice component and later “looked up.” Many people here are painfully aware of that limitation.

@gvalero You want to write the array to the spreadsheet because you want to do some processing there with it that Glide can’t do right now, correct? What processing specifically is it that want to do with the array?

Write an array column (via Lookup) to Google sheet

Report a Bug

Jeff_Hager July 25, 2020, 9:45pm 4

For reference:

Topic		Replies	Views
Lookups from multiple realations Ask for Help	9	1169	July 25, 2020
Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts Ask for Help	125	15467	September 9, 2022
How to retrieve data after duplicating and relation in Google sheets Ask for Help	17	1078	February 24, 2021
👪 Make Array — the newest column type! Community Resources tutorial	38	4957	October 22, 2022
Comma separated values in a single sheets column to drive the match multiple relations in Glide? Feature Requests	13	2372	February 9, 2020

Write an array column (via Lookup) to Google sheet

Related topics