UrTrix - UnRevealed Tricks

An order form, created in Google Forms, requires customers to provide their full name, the item quantity and whether home delivery is required. The final bill amount is calculated with a simple formula in Google Sheets. // Item cost is $99 per unit. Delivery cost is $19. = IF ( ISNUMBER ( C2 ) , SUM ( C2 * 99 , IF ( D2 = "Yes" , 19 , 0 ) ) , ) The Google Sheet owner has entered the formula across all rows in the Total Amount column so that the value is automatically calculated when a new form response is submitted. The problem is that the formulas in Google Sheets are automatically deleted when new responses come in. That’s the default behavior and even if you protect the column range, the formulas in the cell will be erased on new rows. How to Prevent Formulas from Deleting There are several workarounds to this problem. Use an ARRAYFORMULA Instead of adding formulas inside individual cells of the column, add an Array Formula to the first row of the colu...

You have been using Google Docs to write documents and essays but did you know that the same editor can also be used to write and run JavaScript code? It is no replacement for a dedicated IDE like Visual Studio code but Google Docs can be used as a JavaScript playground to quickly run code snippets. Here’s a sample document written in Google Docs and the document body contains a JavaScript function that calculates the number of days left until the next Christmas. Go to the Code Runner menu, choose Run JavaScript and the output of the function will display in a popup. See demo Code Runner in Google Docs Internally, there’s a little Google Apps Script that is doing the magic. It reads the body of your Google Document as a text string and uses the eval() function of JavaScript to evaluate the text. /** * @OnlyCurrentDoc */ function codeRunner ( ) { const doc = DocumentApp . getActiveDocument ( ) ; const text = doc . getBody ( ) . getText ( ) ; const re...

The Document Studio add-on helps you automatically send text messages when a new Google Form is submitted or when new rows are added to Google Sheets. You can thus build workflows that send text reminders when the invoices are due. Or you can get notified instantly when people fill out your Google Forms. The SMS Workflow The text messages in Document Studio are sent through Twilio but the app can be integrated with any SMS service as long as the service offers an API for sending text messages programmatically. You may use TextMagic, SimplyTexting, Vonage, ClickSend, RingCentral, or any SMS service of your choice. For this example, we have a Google Sheet that contains the customer’s name, phone number, invoice number and the amount that is due. Column A of the Google Sheet, titled Send Reminder contains checkboxes and the SMS should be sent only for rows where this checkbox is selected. Format the Phone Numbers The phone numbers in Column D should conform to the E.164 inte...

A school provides email accounts for students that are enrolled in high school. The school has published a Google Form and any student can put their name in the form to request access to an email address. Parent’s consent is required though. When a child submits the request, an email confirmation is sent to the parent of the child for them to provide consent before the student’s email address can be created. Lookup Parent’s Data in Google Sheets The key here is that an email should be sent to the parent of the student. This data itself is not available in the Google Form but the school maintains a Google Sheet with the parent’s data and we’ll do a lookup to fetch the parent’s name and email address based on the student’s name. When the Google Form is submitted, a new row is added to the Google Sheet with the form response. We’ll add two new columns to the form response sheet that will lookup up the parent’s email address and name from the parent records sheet. It uses Array...

Whether it is a wedding party or a business conference, those tent-shaped place cards are ideal for helping your guests find their seats at the event. This tutorial explains how you can create personalized place cards with the names of your guests and the table number to which they have been assigned. We’ll use Google Sheets to create the party seating plan, Google Slides to design the place card template and Document Studio to generate those place cards as PDF files in Google Drive. Let’s get started. Create Guest Seating Plan Create a new spreadsheet inside Google Sheets ( sheet.new ), enter the names from your guest list and assign a table number for each guest. Make sure that the two columns have a title as shown in the screenshot above. Also see: Create Business Cards from Google Sheets Create Place Card Template Create a new slide deck inside Google Slides ( slides.new ) or use this readymade template to quickly get started. Go to File > Page Setup and change t...