I currently have a sheet that uses the GoogleFinance operator to pull stock info automatically. I then use formulas in other columns to automate other information accordingly. I want to be able to add user data in the glide app (i.e. stock ticker), and have all the formulas run on the new row of data that Glide appends to the sheet. Normally an arrayformula would accomplish this fine, but in this case an arrayformula won’t work with GoogleFinance since apparently GoogleFinance acts somewhat as an array itself.
Looking for a way to have user data added to the sheet and still accomplish the above. Any help is appreciated.
The best way would require some Google Scripting to write the formula to the proper cells when a row is appended. It would also entail setting up a timed trigger to automatically run to check for those new rows. An alternative way would be to have a sheet that has all of the formula pre populated, even on the empty rows. For that you would need to get an Certified Expert, who has scripting skills, or search online for how to write formulas to cells when a new row is appended. It would not be easy for a novice.
The second way would be to have a second sheet that say has 1000 or more rows. Call it the WorkerBee sheet. This sheet would use arrayformulas to pull the required information from the main data sheet, which I assume would be a ticker symbol into column A. Column B, C, D … would have the finance formulas populated for all 1000 rows of the sheet. You would then do a relation to that WorkerBee sheet from the main data sheet on the symbol. Then a series of Lookup columns returning the data needed back to the main data sheet. Here is an example app that shows this technique. https://arrayf-alt.glideapp.io/
Note one caveat. Google finance does limit the number of calls you make to it from a given sheet over a given time period. I don’t know the limits but I have run into that issue in the past for large sheets.