UrTrix - UnRevealed Tricks

Dates in Google Sheets are internally stored as numbers and the value is equal to the number of days since 30th December 1899, midnight. The time values are stored as a fractional number. For instance, if the date in a cell is Jan 1 1990, Google Sheet will store the cell value as 2. If the date has a time component, say Jan 1 1900 6 PM, the internal date value will be 2.75. The date and time values in Google Sheets are commonly displayed in the dd/mm/yyyy format, depending on your Spreadsheet locale, but this display format can be easily customized using the built-in TEXT function. For instance, a date like 15/10/2021 can be displayed as Oct 15 2021 or in a long format like Friday, October 15 2021 or you may extract the time component and display it as 03:52 PM . Convert Date Formats in Google Sheets The TEXT function of Google Sheets allows to convert the date and time values in a sheet to a different format. It takes two parameters: The date or time value to be conver...

Microsoft PowerPoint has this really useful feature that lets you to easily resize multiple shapes in a slide to the same size. You can select the shapes you want to resize and then click on the Format Pane button. Here, under the Size and Position tab, you can resize the shapes to the required size. Google Slides doesn’t allow you to resize multiple shapes in a slide but you can use Google Apps Script to do the same thing. Go to the Tools menu and select Script Editor . Here copy-paste the code below and click on the Run button. It will match the height and width of the first shape in the slide and resize all the shapes in the slide to the same height and width. The shapes are also reposition such that there’s equal distance between the shapes and the top edge of the shapes are in alignment. const resizeSlideShapes = ( ) => { const SPACING = 20 ; const [ slide ] = SlidesApp . getActivePresentation ( ) . getSlides ( ) ; const [ baseShape , ... targetSha...

Your organization has a few vacant positions and you are planning to use Google Forms to prepare a pre-interview questionnaire for job applicants. You have created a form and it has all the standard fields where candidates can fill-in their name, email address, phone number, zip code and other information. The form has been prepared but before you make it live, how would you ensure that candidates have entered data in the correct format? And even if the format is proper, is the data itself valid? Can you add a CAPTCHA to Google forms to prevent spam bots? Can you include a profanity filter to block people from submitting entries that include obscene words? When you are expecting dozens, or even hundreds, of responses in your Google Forms, it is always a good idea to have some rules in place and the respondents’ data be matched against these rules even before they submit the form. For instance, if your form is asking for a person’s year of birth, and the applicant’s age should be bet...

A folder in Google Drive contains a bunch of CSV files and you are required to write a Google Script to find a particular value in the CSV files. The solution is simple: Use the Drive API to get a list of CSV files in the specified folder. Parse the CSV files one by one using the Utilities.parseCsv() function. Read the CSV file, line by line, until the value is found and return the line number. const findContentInCSVFiles = ( folderId , searchString ) => { const folder = DriveApp . getFolderById ( folderId ) ; const files = folder . getFilesByType ( "text/csv" ) ; while ( files . hasNext ( ) ) { const file = files . next ( ) ; const fileContent = file . getBlob ( ) . getDataAsString ( ) ; const linesOfData = Utilities . parseCsv ( fileContent , "," ) ; let found = false ; let lineNumber = 0 ; for ( ; lineNumber < linesOfData . length && ! found ; lineNumber += 1 ) { ...

You run a coffee shop and you are looking for a spreadsheet formula to quickly look up prices of the product that your customer has ordered. You have the price matrix stored in a Google Sheet with the names of beverages in one column and the quantity-wise prices in the adjacent columns. When a customer selects their favorite beverage and the cup size, you can use the MATCH function to find the relative position of the column and row in the price table that matches the selected beverage and quantity. Next, use the INDEX function to find the actual price of the beverage in the selected quantity. In our Starbuck Coffee example, the coffee prices are stored in the range B2:B11. The customer’s beverage name (Caffè Mocha in this example) is stored in the cell G3. The following MATCH function will return the relative position of the selected beverage from the list of beverages. =MATCH(G3, $B$2:$B$11, 0) The third parameter of the MATCH function is set to 0 since we want the exact ma...

A folder in Google Drive contains a bunch of CSV files and you are required to write a Google Script to find a particular value in the CSV files. The solution is simple: Use the Drive API to get a list of CSV files in the specified folder. Parse the CSV files one by one using the Utilities.parseCsv() function. Read the CSV file, line by line, until the value is found and return the line number. const findContentInCSVFiles = ( folderId , searchString ) => { const folder = DriveApp . getFolderById ( folderId ) ; const files = folder . getFilesByType ( "text/csv" ) ; while ( files . hasNext ( ) ) { const file = files . next ( ) ; const fileContent = file . getBlob ( ) . getDataAsString ( ) ; const linesOfData = Utilities . parseCsv ( fileContent , "," ) ; let found = false ; let lineNumber = 0 ; for ( ; lineNumber < linesOfData . length && ! found ; lineNumber += 1 ) { ...

Stripe payment links make it easy for you to accept credit card payments from customers anywhere in the world without even having a website. You can use the Stripe dashboard to generate payment links and then send the links over email, WhatsApp, SMS, or share them on your social media pages. A limitation of Stripe Payment links is that you can only generate them manually. Stripe has a feature-rich API but it doesn’t allow you to generate payment links automatically. Stripe Payment Link Generator If you are looking to generate custom payment links for Stripe in bulk and send them to your customers, you can consider using Stripe Checkout. These are payment forms hosted on the Stripe website and allow you to collect only payments your customers. It is important to note that Stripe Checkout sessions will automatically expire after 24 hours. As an alternative, you can use the Stripe API to generate invoices and email the invoice link to your customers. Generate Stripe Checkout L...