How to Change the Date Format in Google Sheets

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:

1. The date or time value to be converted.
2. The preferred format to convert the date or time value to.

=TEXT(A1, "MMMM d, YYYY")

Here are some sample date formats that you can use in the second parameter of the TEXT function:

Date and Time Pattern	Result
MMMM d, YYYY	October 21, 2021
dd-MMM-YYYY	08-Dec-2021
MMM d, YYYY	Dec 3, 2021
dd, MMMM DD YYYYY	Tue, October 19 2021
ddd	Tuesday
d/MM/YYYY	30/11/2021
dd MMM YYYY	06 Feb 2022
mmm-yy	Oct-21
dd/mm/yy h:mm	22/10/21 22:31
hh:mm:ss am/pm	01:11:39 PM
h:mm	14:23
h:mm am/pm	9:58 PM
MMM-dd h:mm am/pm	Oct-20 10:37 PM
MMM DD, ‘YY h:mm am/pm	Oct 31, ‘21 10:34 AM

You can view the complete list in this Google Sheet.

Repeated Pattern in Custom Date Formats

The placeholders (like d, m or y) have different meanings depending on the number of pattern letters.

For instance, if the input date is October 5, the format code d will display the day of the month as 5 but if the format code is dd it will display zero-padded value as 05. If the format code is ddd, the result is an abbreviated day of the week Tue but if the format code is dddd, the full day of the week as Tuesday gets displayed.

Similarly, for the month placeholder, mm will display the zero-padded numerical value but mmm and mmmm will display the abbreviated and full month name respectively.

Date Formats with Array Formulas

If you have a date column in Google Sheets and you want to display the date in a different format, you can use an array formula in a new column to convert the dates.

Assuming that the date column is in cell A1, you can use the following array formula in the first cell of an empty column to display the same date and time value but in a different format.

=ARRAYFORMULA(
  IF(ROW(A:A)=1,"New Date Format",
  IF(ISBLANK(A:A),"",TEXT(A:A, "MMMM dd, YYYY"))))

This can be very handy for Google Sheets that are storing Google Form responses. Google Sheet will always show the response timestamp in your locale but you can add a new column to display the date and time in a different format.

Also see: Google Sheets Formulas for Google Forms

from Digital Inspiration https://ift.tt/3AEC6iU

Make all Shapes the Same Size in Google Slides

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, ...targetShapes] = slide.getShapes();

  // Is the shape rectangular or triangular
  const shapeType = baseShape.getShapeType();

  // Get the shape height and width
  const height = baseShape.getHeight();
  const width = baseShape.getWidth();

  // Get the co-ordinates of the base shape
  const topPosition = baseShape.getTop();
  const leftPosition = baseShape.getLeft();

  targetShapes
    .filter((shape) => shape.getShapeType() === shapeType)
    .forEach((shape, index) => {
      shape.setHeight(height);
      shape.setWidth(width);
      shape.setTop(topPosition);
      shape.setLeft(leftPosition + (width + SPACING) * (index + 1));
    });
};

The Google Script can handle both Rectangle and Triangle shapes. Please note that the first shape in the slide is the base shape and will determine the height and width of the other shapes in the same slide.

Match Rectangle Shapes

Match Triangle Shapes

You can use the same technique to recolor shapes and make all shapes the same color. You can play around with the shapes template here.

from Digital Inspiration https://ift.tt/3mGHNIg

Useful Regular Expressions for Validating Input in Google Forms

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 between 25 and 50, they should only be allowed to enter a number between 1970 and 1996 in the year of birth field.

Regular Expressions in Google Forms

Google Forms makes it relatively easy to add such advanced date validation rules to individual fields through Regular Expressions (or regex or regexp). Think of them as search patterns and every character entered in a form field is matched against that pattern - the form can only be submitted if the patter and the user-input matches.

Let’s understand this with a real-world example.

Say your Google form expects the user to enter their year of birth. At the time of designing the form, expand the “Data Validation” section below the form field (see screenshot above) and choose Regular Expression from the drop-down. Next select “Matches” in the other drop-down and enter the following regex:

^19([78]\d|9[0-6])$

The field will now accept input value like 1977, 1995 but would reject other values that fall outside the 1970..1996 range.

Regular Expressions for Common Form Fields

A regular expression may appear gibberish but they aren’t so difficult to read and understand if you can know the basic rules of the language. What you see here is a compilation of some useful regular expressions that can be used to validate common form fields like URLs, phone numbers, zip codes, dates, etc.

1. Postal Address - allow only alphanumeric characters, spaces and few other characters like comma, period and hash symbol in the form input field.

[a-zA-Z\d\s\-\,\#\.\+]+

2. ZIP Code - the regex allows ZIP codes in standard formats and it matches both US and Indian zip codes.

^\d{5,6}(?:[-\s]\d{4})?$

3. Date - accept date input in the mm/dd/yyyy or mm-dd-yyyy formats.

((0[1-9])|(1[0-2]))[\/-]((0[1-9])|(1[0-9])|(2[0-9])|(3[0-1]))[\/-](\d{4})

Also see: Get Google Form Data by Email

4. Email Address - the regex below should match most common email address formats, including Gmail aliases that accept the ”+” sign but there’s no perfect solution.

[a-zA-Z0-9_\.\+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-\.]+

5. URL (Web domain) - this is useful for fields that require the user to enter their website address and it even matches the upcoming TLDs like .directory or .restaurant. The other regex matches YouTube URL including those using the youtu.be domains.

https?\:\/\/[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,}
https?\:\/\/(www\.)?youtu(\.)?be(\.com)?\/.*(\?v=|\/v\/)?[a-zA-Z0-9_\-]+

6. Character Limit - the default text box in a Google form allows users to input any number of characters but you can impose a limit with the help of regular expression. Here we limit the input to 140 characters much like Twitter.

[\w]{1,140}

7. Phone Numbers - these are often a series of numbers preceded by an optional ”+” sign and the area code may be inside brackets.

\+?\(?\d{2,4}\)?[\d\s-]{3,}

8. Price (with decimal) - if a form field requires users to enter a price of an item in their own currency, this regex will help. Replace the $ sign with your own currency symbol.

\$?\d{1,3}(,?\d{3})*(\.\d{1,2})?

9. Complex Password - only accept a string that has 1 uppercase alphabet, 1 lowercase alphabet, 2 digits and 1 special character. Also the minimum allowed length is 8 characters.

(?=.*[A-Z])(?=.*[a-z])(?=.*[0-9].*[0-9])(?=.*[^a-zA-Z0-9]).{8,}

10. CAPTCHA - Google forms do not offer CAPTCHAs but you can create one using regex. Here’s a simple captcha that requires users to answer a simple question - what is 2+2?

^(4|[Ff][Oo][Uu][Rr])$

Also see: Regular Expressions for Gmail Search

11. Word Limit - If you would like to limit the number of words that a user can type in the input field of a Google Form, there’s a regex for that as well. In this case, we only allow any input that has between 10 to 15 words:

^[-\w]+(?:\W+[-\w]+){9,14}\W*$

from Digital Inspiration https://ift.tt/2YyPI1Z

Improve Performance of Google Apps Script with Memoization

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:

1. Use the Drive API to get a list of CSV files in the specified folder.
2. Parse the CSV files one by one using the Utilities.parseCsv() function.
3. 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) {
      const line = linesOfData[lineNumber];
      found = line.find((element) => element === searchString);
    }

    if (found) {
      return `${searchString} found in line #${
        lineNumber + 1
      } of file ${file.getName()}`;
    }
  }
  return "String not found :(";
};

Optimize Google Script Performance

The code to read CSV files and find the required value is simple but not efficient. You’ve to perform the same expensive operation for every value that you have to search in the folder of CSV files.

Memoization is a simple optimization technique that can be used to improve the performance of your Google Apps Script code. The basic idea is that you cache the results of an expensive function call using closures. If the function is called again with the same arguments, the cached result is returned instead of calling and executing the function all over again.

const memoize = (func) => {
  // Cache for storing the previously computed results
  const cache = {};
  return (...args) => {
    // Serializer to convert N arguments to a string
    const key = JSON.stringify(args);
    if (typeof cache[key] === "undefined") {
      cache[key] = func(...args);
    }
    return cache[key];
  };
};

const memoizedFindFunction = memoize(findContentInCSVFiles);

const findContentInFiles = () => {
  const FOLDER_ID = "<<folder id>>";
  const SEARCH_STRING = "hello world!";
  const response = memoizedFindFunction(FOLDER_ID, SEARCH_STRING);
  Logger.log(resonse);
};

The memoization function is called with the arguments of the original function. The result of the function is stored in a cache and returned when the same arguments are passed again.

from Digital Inspiration https://ift.tt/3AtLvtJ