Often, in script functions, I have to search all rows in a sheet to locate a set of rows matching a criteria. This is dynamic criteria. E.g. Just before accepting an invite, check is there an existing invite for the same email address. Email address is the dynamic filter parameter.
What I do is this. I have a sheet named “Search XYZ sheet” and then on first cell, I put a formula with query which pulls data from the desired sheet and pass parameters to the query function. Now from the script function, I set the formula based on dynamically passed arguments. Read the rows and then make decision about existence of what I am looking for.
I have some doubts:
How will script invocation concurrency be handled? Say n users simultaneously trigger an event (say row added) which calls the function. Is there a chance of calls / parameters / results getting mixed up?
How can the user data that has been submitted to the form and entered into the Google form sheet be automatically entered into the new sheet … ?? I use the IMPORTRANGE formula but it doesn’t work … if this can be it is very good for the list method to be a Seller in the marketplace…
@mthakershi I’m trying to remember how your app looks. Correct me if I’m wrong, you have a public app where a user can enter their email or the event coordinator’s email and and invite code. Then they can RSVP to that event, right? Going off of that assumption, is it possible to eliminate the script entirely and instead use a template to join email and event id together, then create a relation to whichever sheet you are checking for existing emails and events (using a similar template column), then set visibility on the RSVP button based on if the relation column is empty or not.
Depending on your script, I don’t think you would have to worry about concurrent calls as long as neither call is updating the same field. Hard to say though without seeing it. I try to avoid scripts if at all possible.
@MI_I A simple formula like ={‘SheetName’!A:Z} should allow you to duplicate data into a new sheet. Try that and see if it works better.
First of all, are you trying to import from an entirely different spreadsheet into your existing spreadsheet?A few things I’m noticing in your formula. You are using curly brackets instead of parenthesis. I’m not sure if that’s a regional thing like (, vs ;). I think you should get rid of the #gid=… portion of the url. The gid indicates a specific sheet tab in the spreadsheet. Finally, I’m seeing some extra spaces that may be causing issues.
Try this formula. If it doesn’t work, replace the parenthesis with curly brackets: =IMPORTRANGE ("https://docs.google.com/spreadsheets/d/1bt65NRMDtYsGuQxqjrHSEvIZCnnozrAJWzEvYprWEno/edit"; "Join to Seller!F3:F6")
You can have a tab of Seller’s products. Apply a filter to only show the products where the email matches the signed in user. Turn on editing for this tab.
Next create a duplicate sheet and use the ={‘SheetName’!A:Z} formula to pull in all of the products from the product sheet. This will become a new tab with no filters and editing turned off. That way only sellers can edit their own products in the first sheet, but everyone can view all of the products in the second sheet.
Hi Jeff. You’re right about concept of my app. I get your point about the relation too. I come from relational DB background so I have structured my spreadsheet just as a relational DB with PK and FK. Maybe that translates into using more # of rows towards glide limit but I like structured data more than dumpling everything in one sheet.
Now, I use scripts mainly for background processing. E.g. Sending emails out based on invites an organizer has created. I just can’t stop using scripts. I think it is too powerful and very much needed to create a complete system (glide + spreadsheet magic + scripts + third-party APIs). I will give you an example about a possible conflict scenario I am thinking.
One of my background script created an “Invite” row based on the result of an invitation email sent. But before I append a row to the “Invite” sheet, I want to make sure the same email isn’t already invited for the same event. To do this, I created “Invite search” sheet which basically has this in the first cell: =QUERY(Invites!C:X, “where C = ‘EVT_1’ AND F = ‘mthakershi@gmail.com’ AND V = TRUE”,-1)
In my script function, I set this formula and check for row count in “Invite search” sheet. If I get > 0, I skip redundant invite creation. Otherwise I append a new row. So my doubt regarding concurrency was that if say 10 users invoked this function in a concurrent way, is there a chance of unpredictable behavior / parameters getting mixed up?
For a product that’s from a company like google it seems obvious that it will handle it but still I want to make sure I am doing it right and not going to hit an issue in production.
1 . In you filter, select the column that contains the user’s email. It looks like you have column M, which is the ‘Email’ column. Once you’ve selected that column, the second dropdown (shows ‘is’ in you screenshot), should have an option that says ‘Is Signed In User’. The textbox where you have ‘User’s email’ will go away. This will filter all rows where the email in the row will match the email of the signed in user.
2 and 3. You only need to place the formula in cell A1. It will bring in all of the data from the Join to Seller sheet. The formula should look like this ={'Join to Seller Service'!A:Z}. There is no need to drag or duplicate anything. It will be automatic. There’s also no need to use IMPORTRANGE unless you are trying to get data from an entirely different spreadsheet. (Not sheet…Spreadsheet)
I agree. I like to structure data like a relational database too. It’s easier to understand and is future proof to modifications in the sheet structure.
I’m not opposed to scripts. I use them sparingly. I like trying to find solutions with sheet formulas instead because it’s easier to follow and easier to explain to others. Explaining a script and how to set it up to other users is next to impossible for me and a bit out of scope of many users abilities.
I can maybe see a potential problem if the Invite search sheet is not updated in real time during each loop of the script. Is there a way you could perform the query within the script, so on each loop, it would query the Invite sheet looking for a TRUE value? Even better would be to eliminate the Invite Search sheet and loop through Invites sheet to only send emails when the V column value is FALSE, then immediately set the value to TRUE. I only say this because my app uses scripts that get called on spreadsheet update. I also have heavy usage of queries and other formulas. I don’t have the same setup as you, but while watching the sheet calculation progress bar, it almost acts as if it’s processing each call one at a time. I really don’t know if google does one call at a time or does multi-threaded processing, but the way my stuff is set up, it eventually works everything out, even if some calculations are done multiple times due to multiple concurrent inputs. If there is any doubt, I would restructure the script to only loop through the Invites sheet instead of updating values in the Invites sheet, which updates the InviteSearch sheet and then looping through that.
Jeff _Hager.
First in the Menu I create a Join technic form for those who want to sell their services, then their submitted data can automatically be entered into the Join technic file on the sheet as Seller data. Then I duplicate it with the formula = {‘Join technic’! A: Z} until it becomes a new Technic file. I succeeded.
Next I want only sellers to edit their service profiles, while visitors / buyers can only look around.
I have duplicated file Technic to be Seller technic from formula={‘Technic!A-Z’}.
Then I have filtered and duplicated new files and made new tabs. But I am confused 2 views appear like below:
How to use it so that visitors / buyers can see the seller’s technic product profile and can’t edit it ?? Without looking at 2 display views, how are they choice tabs display?.
As for technical seller, how can they see and edit their profiles ?? Without looking at 2 display views. how are they choice tabs display?.
So you have a Join Technic sheet which is a profile that the user fills out. This is located in the upper left menu, correct? Then you have duplicated the data into the Technic sheet and then duplicated that data into the Seller Technic sheet. I think it would be unnecessary to have 3 sheets for this. If you look at the Instagram template, there is a profile tab that will store a form button for the user to create their profile. Once they create their profile, then the components change to show only their own profile that they can edit. There is a separate Users tab, which works how your Seller Technic tab could work.
There is also a way to do this with one sheet. It only works when you can display a single record for a user (details style layout) at the to level of the tab. Your original question was about products. I don’t think this method would work for products. It would work for your Seller scenario. First you create the profile tab with the join button that fills the Join Technic sheet. Once there is a record you can turn on editing and signed in user filtering. The user will only see and be able to edit their own profile. In the Seller Technic tab, point it to the Join Technic sheet and turn off editing and filtering. You would no longer need the Technic and Seller Technic spreadsheets and you wouldn’t have to duplicate the seller data.
In the end, I’m picturing a profile tab in the menu of the app and just one seller tab at the bottom of the screen. I recommend looking at the Instagram template. I think that would be very close to what you want.
Sorry, I’m not sure I understand. What’s bot working for you? It looks like you just need to click on create profile, which will be used to create a record in the Profile Technic sheet. Once you have a record in that sheet, the app screen should change and you should see your profile.
yes I have successed to create from the Create User’s Profile button to sheet but when I go to the Menu and select User Profile buttom, so my profile photo does not appear and also the page cannot be edited…
Are you filtering the profile page by signed in user? Are you using a different email than what you are using for your Glide account? If the emails don’t match, try changing the email with the ‘Preview As’ button on the left hand side.
So, you never once tried changing the ‘preview as’ like I asked and then you deleted the filter for no reason? Of course, then you are always going to see the first record in the sheet.
