Google Sheets formulas & native apps

We’re developing an app that relies heavily on the IMPORTDATA function and several ARRAYFORMULAs, QUERYs, and other such Google Sheets functions to chop a single column of imported ICAL (not a Google calendar) data up into columns and rows, so Appsheet can process the data as ordinary records.

This is working fine in prototyping, and I expect it will work when deployed to our internal users, but my concern has to do with deploying as a white label native app, which we hope to do at some point in the future.

My question is whether these Google Sheets functions and formulas (and scripts for that matter) compile somehow into the exported native app along with the functions, bots, etc. I’m entering via Appsheet or do I have to figure out how duplicate this processing in the Appsheet environment?

Also, there are multiple tabs in our underlying Google Sheet for processing ICAL data from multiple urls, and the resulting data is then aggregated in a single sheet with a single query of all of the tabs. I didn’t create Appsheet tables for each of those individual sheets, just for the master sheet with the aggregated data. Again, this works fine in the prototype, but my concern is that if I don’t have an Appsheet table for each of those tabs they won’t “exist” in the exported native app and I won’t be able to call them.

My first objective is to make an app that works for our organization, but I don’t want to have to tear it all down and redesign it later if it turns out all or part of the underlying Google Sheets structure won’t make it into the published app.

Any advice on how I can do it right the first time?

1 7 207
7 REPLIES 7

@Steve

This account is making a number of these senseless posts. I assume it’s just someone posting random stuff. Is there anyway to have the account removed??

Yes, appsheet formulas do. When you access a Google tab that has formulas in it, AppSheet will try to use that formula – if possible. It has to be a formula that can apply to the column of data. In those cases, in the column’s Auto Compute section you will find the imported formula re-constructed to fit in the AppSheet schema. See image below for location.

I do not use formulas in ANY of the tabs I use in the apps I build. I always build assuming the app and data will migrate to a database someday. If there were formulas in the sheet, the migration to a database would be a nightmare.

I might have formulas in supporting tabs - i.e. tabs that AppSheet apps never see.

I guess this depends on your intent. While I avoid formulas in my app datasources, that doesn’t mean it is a must. I will rely on others whitelabel experiences to respond to this part.

Location of the Spreadsheet Formulas inserted into AppSheet

In this particular case, I’m not sure how else I can do it. I’m importing data from an iCal feed via a third-party url. I know I can create a table from a Google Calendar source, but the only sources I have are raw iCal data, which I’m bringing in with IMPORTDATA functions in Sheets. This data comes in one long column, so I’m divvying it up into rows and columns with a selective QUERY of the imported data, wrapped in an ARRAYFORMULA. That gives me nice-looking records, but the cells actually contain formulas, not static data. Maybe I could set up a bot to copy and paste values into another tab?

Understood. It is not a hard and fast rule to avoid formulas.

You could probably create a Google script to parse the ICAL data and insert it into your source sheet.

What is your intent of white labeling? Are you hoping to make the app available only to internal users? Or are you hoping to offer the app up to other companies?

That sort of goes to my original question, but it sounds like you’re saying an Apps script that currently lives in my Google sheet will ultimately end up in the published app. Seems like a script running on a time-based trigger would probably run faster than dozens of foot-long formulas anyway.

The short-term goal is to create an app for scheduling cleaning and maintenance at various short term rentals for multiple clients. There are plenty of apps out there that purport to do this, but we haven’t seen anything available for any price that does what we need it to do. This leads me to believe that there would be a market for an app for other property maintenance companies like ours, and also for owners of self-managed properties.

Sorry, I did not mean to imply this. Google Scripts will not be published within the white label app. And now that I think about it, I’m really not sure how sheet formulas are handled in the app to be published.

The big concern I gathered was getting the ICAL data parsed and into the sheet used by the app. I believe that you can use Google Scripts for that with that processing remaining outside of the published app.

Of course, I am not sure how that fits in with your plans to sell the app. It seems you would want something that is more native to the app.

Okay, thanks for clearing that up.

Unfortunately there’s no API or other way I’m aware of to hook my calendar feeds directly into Appsheet from the source, so I guess I’ll stick with my Google Sheets formulas for the time being.

Since most of mine are arrayformulas which only appear in the first row, it doesn’t look like they’ll map over well. It’s possible I can rejigger them into ordinary formulas for each record though. No clue how Appsheet will take to Google’s SQLish query language though.

The parsing I have works fine, but evidently won’t work in a published app as is. And yes, it would be pretty awkward if the end user also had to set up sheets manually in Google sheets or subscribe to some third party integration. It’s sounding like I might not be able to publish through Appsheet, but I do appreciate your guidance!

Top Labels in this Space