How To Insert Emojis In Google Sheets

How To Insert Emojis In Google Sheets

Using emojis to spice up your Google Sheets calculations may be fun and effective. Although there are many fantastic ways to add emojis to Google Sheets, I think using the built-in CHAR function is the best solution.

How To Insert Emojis In Google Sheets

Copying the hex code of any desired emoji from unicode.org is a clever technique to move forward. The hex value should then be converted into its decimal counterpart using the HEX2DEC function. This decimal number can then be passed into the CHAR function to produce the equivalent emoji symbol.

Let’s deconstruct it:

Use the following formula to add the emoji:

=CHAR(HEX2DEC("1F600"))

The following equation will give you the emoji’s hex value if you’re curious:

=DEC2HEX(UNICODE("😀"))

But we’re not simply here to show you how to use emojis in Google Sheets; that’s not our main goal. It actually focuses on solving any problems that emojis could introduce to your Google Sheets-related activities.

The problem with Emojis in Google Sheets:

You may rely on Apps Script to help you convert a Google Sheet into a PDF document programmatically. The caveat is that if your Google Sheet contains emoji symbols, the PDF conversion procedure may encounter a problem and result in a bothersome 500 error. This glitch is caused by a known fault that is currently plaguing Google’s systems; regrettably, a remedy has not yet been made available.

How To Insert Emojis In Google Sheets

Replace Emojis in Google Sheets:

Google Extensions including Email, Sheets, and Document Studio using the native conversion engine built into Google Drive to convert spreadsheets into PDFs. However, if your input sheet contains any emoji symbols, be prepared for the PDF conversion to fail owing to this recurring problem.

Your best option for a remedy is to diligently search your spreadsheet files for hidden emoji symbols and remove them before starting the PDF conversion process.

Fortunately, Amit Agarwal has provided a solution through his Google Script. You may make your sheet an emoji-safe PDF refuge by putting the following strategies into practice:

The replaceEmojisInGoogleSheet function can be used as follows:

const replaceEmojisInGoogleSheet = () => {
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheets()
    .filter((sheet) => sheet.getType() === SpreadsheetApp.SheetType.GRID)
    .filter((sheet) => sheet.isSheetHidden() === false)
    .forEach((sheet) => {
      sheet
        .getDataRange()
        .getValues()
        .forEach((row, rowIndex) => {
          row.forEach((cell, colIndex) => {
            if (typeof cell === "string" && /\p{Emoji_Presentation}/u.test(cell)) {
              sheet.getRange(rowIndex + 1, colIndex + 1)
                   .setValue(cell.replace(/\p{Emoji_Presentation}/gu, " ").trim());
            }
          });
        });
    });

  SpreadsheetApp.flush();
};

When you run this script, it will methodically go over your entire page, looking for cells that have emojis in them. Then, it will tastefully substitute whitespace for these emoticons.

The secret is in the pEmoji_Presentation regular expression pattern, which searches for emoji characters. The ‘g’ flag guarantees a global search, making sure that all occurrences are changed. The ‘u’ flag, meanwhile, supports Unicode mode, which successfully controls the use of emoji characters.

You may now easily convert your sheet into a beauty of a PDF without encountering the terrible 500 error that is brought on by naughty emoji symbols.

How To Insert Emojis In Google Sheets

Examine Google Sheets’ magical emoji feature! A beginner’s tutorial on adding emojis using the CHAR function, HEX2DEC, and DEC2HEX conversions can elevate data visualization. Emojis can be easily replaced by smart Google Scripts and avoiding common PDF conversion difficulties. Make dry statistics into interesting stories!

Leave a Comment