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 ) { ...