Emojis in Google Sheets

Learn how to use emojis in Google Sheets and the challenges they pose during conversion of Google Spreadsheets to PDF files.

Emojis in Google Sheets

Emojis can be a fun and effective way to add visual interest to your Google Sheets formulas. There are so many different ways to add emojis in Google Sheets but my favorite option is the built-in CHAR function.

You can copy the hex code of any emoji from unicode.org and then use the HEX2DEC function to convert the hexadecimal value into its decimal equivalent. The CHAR function will take this decimal number as input and returns the corresponding emoji symbol.

// Add the 😀 emoji to the active cell
=CHAR(HEX2DEC("1F600"))

// Get the hex value of 😀 emoji
=DEC2HEX(UNICODE("😀"))

Well the purpose of this guide is not to explain how to add emojis in Google Sheets but the problems that emojis may cause in your production workflows related to Google Sheets.

The problem with Emojis in Google Sheets

If you are to convert any Google Sheet to a PDF file programmatically, Apps Script can help. However, if your Google Sheet contains any emoji symbols, the PDF conversion engine will fail with a 500 error. This issue arises due to a known bug (see issue tracker) at Google’s end and there has not been any resolution so far.

Google Sheets PDF 500 error

Replace Emojis in Google Sheets

Google Add-ons like Email Google Sheets and Document Studio internally use Google Drive’s own conversion engine to convert spreadsheets into PDF files. the input sheet contains any emoji symbol, the PDF conversion would always fail owning to the bug.

The only workaround to this problem is to check your spreadsheet file for any emoji symbols and remove them before performating the PDF conversion.

/*
 *  Replace Emoji Symbols in Google Spreadsheet
 *  Written by Amit Agarwal www.labnol.org
 */

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();
};

The Google Script will now scan your entire sheet, detect any cells containing emojis, and replace those emojis with spaces. After running the script, you can safely convert your sheet to a PDF file without encountering the 500 error caused by emoji symbols.

The \p{Emoji_Presentation} pattern in the regular expression matches emoji characters. The g flag is for a global search (to replace all occurrences) and the u flag is for Unicode mode (to properly handle emoji characters).

Google Sheet Emojis

Amit Agarwal is a web geek, solo entrepreneur and loves making things on the Internet. Google recently awarded him the Google Developer Expert and Google Cloud Champion title for his work on Google Workspace and Google Apps Script.

Awards & Recognition

Google Developer Expert

Google Developer Expert

Google awarded us the Developer Expert title recogizing our work in Workspace

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional title for 5 years in a row

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator award for technical expertise

Want to stay up to date?
Sign up for our email newsletter.

We will never send any spam emails. Promise 🫶🏻