LinkedIn
TwitterAppSheet Office Hours: Data Source Connections Featuring Google Drive Folders
Published on by
Lauren_vdv
| Updated on
Lauren_vdv
| Updated on
Making data accessible, useful, and actionable are key reasons we build apps. Typically, this requires a lot of engineering work, but with AppSheet, even those with no coding experience can create apps with data from a variety of sources - spreadsheets, SQL databases, and many more.
In this month’s AppSheet Office Hours, we’ll provide an overview of the different data sources available to app creators, with best practices for scaling and managing each type. We’ll also walk through how to connect data from folders, featuring Google Drive.
As always, you’ll have the opportunity to ask any questions and receive answers live from AppSheet experts.
Save your spot by selecting the "Yes" button on the right, and ask your questions in advance by posting a comment below.
With our new series of AppSheet Office Hours, it’s our goal to provide a trusted space where you can receive support and guidance along your app creation journey. We hope to see you there!
Meeting information
-
Join the livestream: https://youtu.be/Z3EACZPIawY
Hi, Victor, you can create a unique invoice number that increments as long as you don't set that invoice number as your key. Try this:
- Create an incremented number field. To test:
- In Sheets, create a test sheet with a single record. In my test, I had a sheet called "Scratchpad" with fields for "ID, Base, ItemName" - where "Base" is the field that autoincrements.
- Set the baseline value for your invoice numbers in the first record. In my test table, I set the value of "Base" to "10,000"
- In AppSheet, add the Scratchpad invoices table to your app
- In the AppSheet table, set the definition for the Base field to "MAX(Scratchpad[Base]+1" - now when you add a new record, it will autoincrement based on whatever the highest calculated value is in that field.
- Create a unique invoice number by concatenating AppSheet key and the incrementing Base field:
- In the AppSheet "Scratchpad" table, create a Virtual Column and set that value to the syntax you prefer, e.g., "CONCATENATE([InvoiceNum],"-",[ID]). That guarantees a unique invoice.
- You also could create a field for a non-unique, pretty concatenation to be printed on the invoice itself, e.g., with the InvoiceNum and first four letters of the client.
Tested on this end, and the numbering and concatenation work. Hoping this works or inspires a solve!
Hi Victor, there are several ways to do this.
You can create a column in the sheet for "Invoice Number",and apply a simple expression: [_RowNumber] + 1 but if you do it this way, you can never delete a row, add a row or re-sort the sheet as you will shift the invoice number in some or all rows.
You can prevent this by placing the [_RowNumber] + 1 expression in the "Initial Value" input in Auto Compute, and adding an expression to "Update Behavior" settings in "Editable" : ISBLANK(Reports[Incident File Number]) this prevents changes to the number once it has generated.
You still should never delete a row, as you will be now be missing an invoice number, which is frowned upon in accounting practices:) If an invoice is to be cancelled or voided, change values to zero and have away to indicate status as voided or cancelled.
This will prevent sync from changing the invoice number after initial creation. This will not work with a virtual column, you must add the "Invoice number" column to the sheet and regenerate the columns in Appsheet. This creates permanent storage for the initial invoice number.
Lastly, if you need the number to start at a certain value substitute the 1 in "+1" for the last invoice used number you want the app to start adding from.
Here is a screenshot of the column settings:
Hope this helps you.
I have a couple of questions.
What is the limit on the maximum number of records?
AppSheet often imposes a limit of 100,000 rows.
For example, when importing a sheet, if the sheet has more than 100,000 rows, an error will occur.
However, if we import a sheet with less than 100,000 rows, and then increase the number of records to more than 100,000 rows, there is no problem in using the application.
After all, is this 100,000 row limit only on import?
Are there any plans to add user input fields in the Google Drive data source?
Currently, when we specify a file as a Google folder data source, only the metadata information of the file will be recorded.
Are there any plans to add user-modifiable columns in the future?
For example, if Description and Label values are available in AppSheet, it will be more extensible.
