Hello everyone!
Today, I want to share an incredibly useful add-on called AppsheetPrint. This tool helps extract HTML data from the “web access” action in AppSheet without needing to wait for synchronization or create automation. Below is a detailed guide on how to use this add-on in Chrome and Edge browsers.
Add-ons: Appsheetprint
Latest version 1.0.7
Link add-ons : Google web store
Check out a sample: Sample AppSheet
Youtube : Video demo
Functionality: Export data to Excel, PDF, Print, Google sheet and provide many other customizations.
Scope of use: AppSheet or other similar no-code tools that have the functionality to create an action to go to a website.
Usage: To use this extension, configure your HTML output in the action settings.
Use the structure below for the Action “Go to a Website”:
"https://example.com?html=" & ENCODEURL(SUBSTITUTE(
"
<Html here!>
", "%", "percent"))
Changeable components:
- https: // example. com**:** You can use any URL, preferably a non-functional one for easier processing.
- <Html here!>: Your HTML content. Use AI chat tools for quick HTML creation.
Fixed components:
- ENCODEURL(SUBSTITUTE(
- ?html=
- , “%”, “percent”))
Note:
With : To make the HTML template work on AppSheet, you need to check if it contains double quotes: "
If it does, replace all double quotes: " → with single quotes: '.
This is an example of an HTML paragraph using “double quotes” that DOES NOT work on AppSheet
This is an example of an HTML paragraph using ‘single quotes’ that works on AppSheet
Below is the correct example for HTML to function, using only single quotes (if any) in the original HTML content:
"https://example.com?html=" & ENCODEURL(SUBSTITUTE(
"
<html lang='vi'>
<head>
<meta charset='UTF-8'>
<meta name='viewport' content='width=device-width, initial-scale=1.0'>
<title>Example HTML</title>
<style>
body {
background-color: 'lightblue';
font-family: 'Arial, sans-serif';
}
</style>
</head>
<body>
<h1>This is an example of a html paragraph using 'single quotes'</h1>
</body>
</html>
", "%", "percent"))
To add AppSheet data into the HTML structure, use the format: “&[data_appsheet]&”
Example of an action: “Go to a Website” with the following target URL:
"https://example.com?html=" & ENCODEURL(SUBSTITUTE(
"
<html lang='vi'>
<head>
<meta charset='UTF-8'>
<meta name='viewport' content='width=device-width, initial-scale=1.0'>
<title>Example HTML</title>
<style>
body {
background-color: 'lightblue';
font-family: 'Arial, sans-serif';
}
</style>
</head>
<body>
<h1>This is an example of a html paragraph using 'single quotes'</h1>
<h1>To add AppSheet data into the HTML structure, use the format:"&[data_appsheet]&"
</body>
</html>
", "%", "percent"))
Check out a sample: Sample AppSheet
Here are four action of sample:
Action 1: For example, when inserting an HTML form, use AI tools and replace double quotes with single quotes to avoid syntax error messages (AppSheet will report this error), as explained above.
Action 2: Use the INDEX() function to retrieve data in AppSheet, but it is only suitable for small data tables (from 1-2000 rows).
In AppSheet, the INDEX() function is used to retrieve the value at a specific position within a list or table. However, if you have a very large data table (e.g., millions of rows), using INDEX() to retrieve individual rows may not be efficient. You would need to specify each index (row number) in the INDEX() function, meaning you have to input each row individually. This can be very time-consuming and is not optimal for large and dynamic datasets.
INDEX(Orders[OrderAmount], 5)
Here, the INDEX() function will return the value from the 5th row in the OrderAmount column of the Orders table.
Unfortunately, AppSheet does not have a direct loop, so if you are using INDEX() to retrieve data from a table, you will need to manually enter more row numbers than what your data table actually has in order to process the data. Add-ons can help eliminate any empty rows in the table if there are any.
I really hope that AppSheet will eventually include a direct looping feature to solve this issue.
Action 3 and 4: Avoid using the INDEX() function for better performance.
When Avoid using INDEX(); reduce code complexity when the reference table has too many rows.
Explanation of Comma Handling and Data Processing in Tables when Avoid using INDEX();
When entering data into a table, if the content of any column contains regular commas (U+002C), which are commonly used in sentences, lists, or to separate elements within a sentence, it can cause issues. The U+002C regular comma is the comma you use when typing text through the keyboard in word processors, emails, or on social media platforms.
For example:
- I like reading, writing, and coding.
- This book is interesting, informative, and educational.
The Problem with Using the Regular Comma (U+002C)
If you use the regular comma (U+002C) in the data of your table columns, it can lead to problems when trying to accurately separate or process data. If not handled correctly, it may result in incorrect or misinterpreted data within the table, making it difficult to parse or analyze correctly.
Solution: Create a Virtual Column to Convert Commas
To avoid this issue, you can create a virtual column to automatically convert the regular comma (U+002C) into a different character, such as a semicolon (
, Small Comma (U+FE50: ﹐), Fullwidth Comma (U+FF0C: ，), Japanese Comma (U+3001: 、), or any other similar character you prefer.
In this guide, we use the Fullwidth Comma (U+FF0C: ，) as an example.
By doing so, you can avoid confusion when processing or analyzing the data. The Fullwidth Comma (U+FF0C: ，) is especially useful because it looks similar to the regular comma but is distinct enough to not cause conflicts in data parsing.
Example: If the “Detail” column in your table contains regular commas (U+002C), you can convert them to Fullwidth Commas (U+FF0C: ，) using a virtual column.
This ensures that your table data is accurate and can be analyzed properly without issues caused by regular commas.
Export to Excel, DOCX, PDF, Print from AppSheet
The functions for exporting DOCX, Excel, PDF files, and printing files are already integrated
Export to Google Sheets from AppSheet
Step 1: Create and Deploy the Web App from Google Sheets
1.1. Open Your Google Sheet
Make sure your Google Sheet contains the data and functions required for your Web App.
1.2. Open the Script Editor
In your Google Sheets, go to the Extensions menu and select Apps Script.
1.3. Access the Apps Script Project
Copy the Apps Script file located here
1.4. Deploy the Web App
Once your script is ready, click Deploy in the top-right corner of the Apps Script editor.
- Select Test deployments > Deploy as web app.
- In the Deploy as Web App dialog:
- Set Execute the app as: Me (the script owner).
- Choose Who has access: Anyone (or select the appropriate access level).
- Click Deploy.
Once deployed, you will see the Web App URL.
Step 2: Copy the Web App URL
- After deploying, a dialog box will display the Web App URL.
- Copy the URL by selecting it and pressing Ctrl + C (or Cmd + C on Mac).
Step 3: Paste the Web App URL into AppSheetPrint Add-on
3.1. Go Back to Your Google Sheet
Navigate back to your Google Sheet where you want to use the AppSheetPrint Add-on.
3.2. Open AppSheetPrint Add-on
Go to the Add-ons menu, select AppSheetPrint, and open the options/settings menu.
3.3. Paste the Web App URL
Find the section labeled “Enter Web App URL” in the AppSheetPrint settings.
Paste the Web App URL that you copied earlier into the textbox labeled Enter Web App URL.
That’s It!
Now, your Web App is connected to AppSheetPrint, and you can use it to print or manage data from your Google Sheet.
Additional Tips:
- Test the Web App: Before pasting the URL into AppSheetPrint, open the URL in a new browser tab to check if the Web App is functioning correctly.
- Permissions: You may need to authorize the Apps Script to run as a Web App. Make sure to review and approve the necessary permissions.
- Customize the Script: Modify the Apps Script based on your specific requirements for the Web App