Gsheet doubt

Ask for Help
1

Import range getting stuck. When we change input calues in FE sheet, the output values in BE get calculated but 1 in 3 times, they don’t display back into the FE sheet, unless we make any edits in the BE sheet. If there are any setting changes we need to do or simple hacks we can do, 1 out of the 2 problems (importrange getting stuck & speed slow) can quickly get resolved.

2

What is your use case of using importrange here? Do you absolutely need the data to be in the Sheet vs Glide Tables?

3

yes i have frontend sheet and backend sheet so there for to get value from be sheet i use importrange

4

@ThinhDinh since our Sheet was very huge and we were unable to create a Glide App using the same, we had created a new sheet (called Frontend sheet) which was importing only data elements we needed to display in the App and kept the logic in the Backend Sheet. The interaction between the two was via Importrange. However, following is the issue now:
When we change input values in the Frontend sheet, the output values in Backend sheet get calculated but 1 in 3 times, they don’t display back into the Frontend sheet and hence the App, unless we make any edits in the Backend sheet which triggers iterations. Are there are any setting changes/ hacks (in Glide or Sheets) you could recommend.

We are studying the feasibility of transferring logic to Glide, but given the complexity of our Sheets (logic. intensiity & size), it seems tough. At best we maybe able to move a very small component

5

I recommend before you start on a big change, pay for some glide expert time (hour or two) to talk through your app, your needs, and your vision. I’ve used @Darren_Murphy a lot on my project, plus other experts like @gustavo to help me architect (and Re-architect) my overall system, and part of it over time.

5 Likes
6

Thanks for the suggestion, will love to explore.
@Darren_Murphy could you pls. let me know the charges for the consultation. You may email me at pooja@venex.co.in or if this is a standard package kindly share. Thanks!

7

I’ll respond privately.

8

Thanks for the recommendation @Mark_Turrell !

Hi @Pooja_Kumar, 2 quick questions:
1- how big is your Backend sheet (qty. of rows and columns)?

2- Have you tried to use these functions together in your Frontend sheet in order to narrow results and make your sheet easier to handle?
=Query(ImportRange())

Saludos!!

9

Are you using a pro app?
Are you setting glide to refresh also on edit ?

I have more or less the same set up as you and it is working perfectly.

10

Yes.
On Pro.
Enabled ‘While editing or using the App’

1 Like
11

@gvalero

  1. The Backend sheet has 28 sub-sheets with 4-5 sub sheets having 400 cols. We have limited the rows to 100 so far
  2. We haven’t tried to use Query & Import range together. Can you share some relevant articles/links that we cld go through to understand if this will solve the issue & how to implement it in our case.
    Thanks a ton for your help!
12

Hola de nuevo!

Well, the Query(ImportRange()) combination
won’t be the magic solution but help to simplify results and reduce the columns quantity due to Query() function is able to work directly with columns needed by you and filter data dynamically (like a SQL statement).

Years ago I tested this scenario with a GS with 150k rows and had “good results” but it was another time and an experimental test, not an APP in production.

Here you can read it:

Saludos

13

We just tried doing the below as well:

  1. Delinked our Backend sheet from the Front end one to eliminate importrange. It now has 30 sheets vs. the 12 in the FE sheet + 28 in BE sheet earlier
  2. Changed the source to the delinked sheet
  3. Tested to see if display remains the same for one of the screens & basis results we cld replicate for all - to do this, we modified the sub-sheet linked to the screen such that it has the same name and the data and columns are also in the same order as the original FE. However, when we did the linkage of the screen, the components aren’t correctly mapped and graphs are not rendering.
    Any suggestions on how to fix this?
    Also sharing some screenshots (Original vs Post Data source change) and warnings that came along the way (eventhough, test sheet ‘Employee Entries’ was in the new source file): Source Data Change - Google Drive