Allow an app's column settings to be uploaded from a CSV or Google sheet file

AppSheet AppSheet Q&A
, , ,
1

I presume the following feature idea request has not been made before. I could find requests to export column settings to a CSV as an example below:

However my request is exactly opposite. Allow CSV upload for column settings.

In AppSheet , we need to define each column’s multiple settings -constraints ( valid_if, editable_if, requried_if, show_if etc.) app formulas, initial values , display names and so on for each of the column. The app creator needs to patiently open each column’s settings pane and enter the necessary expressions.

The AppSheet editor does allow some settings such as App formulas, initial values, display names expressions to be set up in the editor’s table view ( example below in screenshot).

image
image1435×772 47.3 KB

However even this option needs each expression individually to be entered or copied in the respective settings window. One cannot copy similar settings for multiple columns at one go.

However there are multiple use cases , wherein in a table, majority of the columns could have exactly similar constraints, app formulas and so on. For example survey forms tables. Most of the questions will have similar constraints, initial values and so on.

It will be great if the app creator can use a CSV upload to set multiple column settings at one go. AppSheet dev. team can suggest a specific format of CSV and the App creators can enter the expressions for multiple column settings at one go in the CSV and then there can be provision to upload that CSV through an AppSheet editor option. Simply copying similar constraints in a CSV or Google sheet is much easier than painstakingly entering these one by one for each column through the AppSheet editor.

Once uploaded, the AppSheet editor can display necessary warnings/ errors as it does now wherever there are issues in expressions etc.

2

Hello @Suvrutt_Gurjar,

This is a very interesting suggestion! I can see how it would save a lot of time for AppSheet developers and even reduce the risk of human errors.

I’m documenting this and add it to the enhancements list for consideration.

Thanks for sharing your ideas!

3

Would this be something different to using the notes parameters on each column for Google sheets. When adding a table with note parameters set in each column header, it automatically configures everything and can be updated using regenerate. Note that if you make manual changes to that column and regenerate the preset parameters wil override again, so definitely clear the notes once done.

AppSheet:{“Type”:“Email”,“IsRequired”:false,“Default”:“USEREMAIL()”,“Required_If”:“ISNOTBLANK([EmployeeName])”,“Show_If”:“USERROLE()=“Admin””,“Editable_If”:“NOT(IN([Status],LIST(“Approved”,“Locked”)))”,“DisplayName”:“Employee Email”,“Description”:“Email address of the employee. Auto-fills with the current user’s email by default.”}

@MultiTech this is down your alley. BTW, Love your note parameter maker in chatgpt

4

@Suvrutt_Gurjar Are you asking for a way in the editor to copy or duplicate to another field on either same or different table?

5

Hello @Denzil_Snyman ,

I am requesting a CSV upload of all column settings in a tabke.

For example if there say 30 questions in a form , which means 30 columns, then upload settings of all these columns at once through a CSV upload.

It will be easy to populate all the similar or identical settings in a CSV, because copy paste across multiple records, cells is much easier in a spreadsheet or CSV.

I will try to add a picture of a sample CSV populated tomorrow.

6

Would something like @MultiTech Standard starting template sample give you this ability. Setting note parameters in Google sheets for each column preconfigures a host of parameters upfront before adding or regenerating table, it will all be pre-configured.

You can preset Enum, Enum lists, etc..

See if this helps in any way, unless you are aware of this, my apologies then

21 minutes in.

7

Using note parameters (the header code you can use on a Google Sheet or Excel file) really help speed up certain things. I’ve been using them for years, even went so far as to build out a whole set of standard tables that I use (Users, Onboarding, Welcome, Notifications, etc.) so I could speed standard build practices up.

  • You can access them, and guides that I’ve made on some final steps to get things situated and functioning in your app, here: AppSheet
8

Hi @Denzil_Snyman and @MultiTech ,

Thank you very much for chiming in. Insights from the platform wizards like you are always so informative.

@Denzil_Snyman I watched and thoroughly enjoyed the video you shared. As always, there are so useful informative nuggets by Matt in the video. His insights on creating a standard template of AppSheet table, so that many of the default columns are always included without fail is so practical approach.

However I believe my request is different . I give below the screenshot of an empty ( CSV or Google sheet ) template first.

image
image1635×512 32.2 KB

Below is the filled in sheet or CSV for a sample hotel room housekeeping checklist form. As we can see many constraints and expressions for display name etc. are similar for multiple columns. So an app creator can fill in all this information in a CSV or Google sheet template with ease. The columns names will be in column A and the column settings in column B and onwards.

Copying, pasting, making minor column specific changes is much easier in a spreadsheet format. Then we can upload this CSV/Google sheet in an option provided in the AppSheet editor.
Once the CSV is uploaded, all columns settings are done for that table in AppSheet by taking CSV data as input. This will especially be useful for repetitive settings. As you can see in the example below, the various settings( Show_if , editable_if, display names) are almost similar for all the question columns of the checklist table.

I have hidden or minimized the unused settings in this example , so that the used ones can be accommodated in one single screenshot.

image
image1867×757 82 KB

I hope this makes sense.

9

@Suvrutt_Gurjar I think I can make sense what your looking for. It will be easier than editing the JSON structure of note parameters in each header.

Although using note parameters can auto configure each column upon import or regenerate, it would be nice to have an easier way to map the configuration of each column like you displayed in your sheet. It should not be hard to accomplish for seasoned coders in Google to incorporate.

For now, I looked at your approach and think it is much cleaner to edit each parameters of column like you displayed it, so In the interim, I am going to try embark on making an interim slightly automated solution to make my parameters setup also easier after seeing your approach.

I will try create a config sheet like your one looks, have a Generate button to get appscript to transpose all rows into the columns with the note parameters configured based on the row parameters set for each column. I can then use that output sheet as the import template to copy where I will use it.

Each columns notes would be generated as follows based on the configure columns.

AppSheet:{“Type”:“Enum”,“TypeAuxData”:“{“Show_If”:“OR(USERROLE()=\“User\”, USERROLE()=\“Admin\”)”,“Editable_If”:”[Cleanliness_Report]=\“In Progress\”“,“EnumValues”:[“Yes”,“No”,“N/A”],“AllowOtherValues”:false,“BaseType”:“Text”,“EnumInputMode”:“Buttons”}”,“Required_If”:“[Cleanliness_Report]=“Submit””,“Default”:““N/A””,“DisplayName”:“IF(CONTEXT(“ViewType”)=“Form”, “Is the work table clean?”, “Work table”)”}

It will most likely minimize doing it in note parameters manually or even in Appsheet itself.

If we could import directly, so much easier than making this workaround.

10

Hi @Denzil_Snyman ,

I fully agree. Using GAS to create the notes will be a great idea and workaround. Thank you very much. It will be a great tips and tricks post as well.

I 100 % agree with you here as well. My request for this feature idea is for making it easier for the entire app creator community, if AppSheet team can implement it.

In the meantime, yes , it will be great to have a GAS do the job as you proposed. An easier to fill in spreadsheet – > GAS – > populate notes columns in the G sheet header — > regenerate and populate columns settings in AppSheet will be a great workaround indeed. Thank you very much for the idea.

11

And then take your Google Sheet with all the configs in them, and make an AppSheet app to make working with the data easier! (^_^)

  • Use an automation to trigger the GAS to rebuild the NPs

You could build out a database of settings, and create a separate table to hold the selected configs for each app - really modularizing the system out. :wink:

12

An Appsheet app NP configurator, Matt, you always seem to take it that extra step and then it becomes even more awesome, thank you for that…

13

بسیار عالی :+1: