Why google sheets save as PDF doesn't show emoji in Cell?

Question

Do you know why some times the emoji icons show normal and some times show as just empty square in google sheets , also when I save sheet as PDF the icon show as line ! , can anyone advise the reason ? what should i do ?

Here is a link of the sheet ( ICON in B24 ) https://docs.google.com/spreadsheets/d/1LRmyNlKSQjADz0bG_-mDf6w0LqEg7IcPgVizXF4C-Xc/edit?usp=sharing

this is the link were i took the icon from .

https://emojipedia.org/emoji/%F0%9F%8C%9E/

Thanks,

Answer 1

It wasn't mentioned:

You can using a pdf printer and it prints fine without the need to export to html first.

"Microsoft print to pdf" works.

Answer 2

The Google status on this request is "Won't fix (Intended behavior)"

And many people cannot understand why a 1990s format like PDF is unable to accept Modern HTML Fonts. Generally PDF translators cannot physically support more exotic fonts like woof or csvg.

Commonly the html styles are

"Noto Color Emoji" (often via Google android platform apps)
"Apple Color Emoji" (used on Apple platforms such as iOS and macOS)
"Segoe UI Emoji" (as used by Windows platform in NotePad etc.)

These can be translated to monochromatic outline fonts for native use in older PDF standard Printout files. At best that may be two flat colours one for the fill and one for the edge. (Black & White is the most common combination.)

Here the source text is on left in windows 11 notepad, as inserted say on a keyboard.
Then we can see in windows 11 notepad (!argh !why two apps same name), what it would look like if on the web as HTML.
Then from that print to PDF is on right and its not same as source !argh !argh, why the differences

so for above the pdf shows

Title: try.txt - Notepad
PDF Producer: Microsoft: Print To PDF
PDF Version: 1.7
Fonts:
CIDFont+F1 (TrueType (CID); Identity-H; embedded)
CIDFont+F2 (TrueType (CID); Identity-H; embedded)
CIDFont+F3 (TrueType (CID); Identity-H; embedded)
CIDFont+F4 (TrueType (CID); Identity-H; embedded)

PDF has enough trouble using TTF monochromatic fonts as shown above so google export fonts in the sample file are  

 <office:font-face-decls><style:font-face style:name="Arial" svg:font-family="Arial" /><style:font-face style:name="Cambria" svg:font-family="Cambria" /><style:font-face style:name="Liberation Sans" svg:font-family="'Liberation Sans'" style:font-family-generic="swiss" style:font-pitch="variable" /><style:font-face style:name="DejaVu Sans" svg:font-family="'DejaVu Sans'" style:font-family-generic="system" style:font-pitch="variable" /><style:font-face style:name="Linux Libertine G" svg:font-family="'Linux Libertine G'" style:font-family-generic="system" style:font-pitch="variable" /></office:font-face-decls>

and the plain text is

<table:table-cell table:style-name="ce1" office:value-type="string"
calcext:value-type="string">text:p😂</text:p></table:table-cell>

but on a web that is naturally colour enhanced since lol is considered naff however in any of the above fonts it would be a null character, or plain text in black and white.

For non font supported characters you could see Fonts: <#4> (Type3; embedded)

As explained by others simply export the html with html iconic characters and then print html to pdf where you can use the html emojis as plain text for find etc.

Answer 3

Google Sheets is now (as of ~28 April 2023) not even removing the emojis, it simply doesn't create a PDF. If using Google Apps Script to create the PDF, error code 500 is returned.

I've reported the bug here: https://issuetracker.google.com/issues/284486971

The only solution that works is removing emojis from Google Sheets before attempting to create the PDF.

This answer from @max-makhrov provides a formula for doing that https://stackoverflow.com/a/70125203/16465606

And here's some code for Google Apps Script to replace Emojis in text strings with blanks.

/** remove emojis from text string */
const emojiRegEx = /\p{Extended_Pictographic}/ug;
const textstring = "I ❤️ emojis 🎉🥳😍 but Google Sheets is not a fan.";
const cleantext  = textstring.replace(emojiRegEx,'');

console.log('emojis removed from text string: ',cleantext);

About the replace() method https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace

Answer 4

The original solution: change the font

if you don't have to choose exact font, emoji containing cells that are formatted with Inconsolata or Sawarabi Mincho fonts are perfectly exported to PDF.

---

UPD. It appears that these fonts export correctly only certain emojis like arrows, card signs, etc... while others popular emojis are not exported correctly.

Notes

As @Osm noted:

failed with Inconsolata and Sawarabi Mincho see this

Noto Emoji seems to be the only font to work. Still Google has tons of fonts and we have no solid proof if some or any of them will work.

Chnaging the font seems to be the only programmable solution at the moment.

---

Vote the issue if you are effected.

Answer 5

Solution

You can print easily with few steps, Colored and with any font.

1 - Add a dot in the right bottom corner to indicate the edge
and Go to File > Download > Web Page (.html)

---

2 - Open the webpage in your browser and print as a PDF

Edit with Pdf editor

3 - Open the PDF file with illustrator or a PDF editor, delete the grid and columns and rows.

Print and Enjoy :)

Answer 6

Why This is Impossible

Google Sheets does not export emoji to .pdf because it does not know what an emoji is. Google Sheets is able to display emoji when doing work not because Google Sheets itself can display emoji, but because your browser/operating system can. If everything was left only to Google Sheets, it wouldn't know how to display emoji at all.

The one exception to this is Noto Emoji (as already mentioned by Max Makhrov). Through this page, it can be seen that no font built into Google Sheets has the ability to display emoji except Noto Emoji. Even then, Noto Emoji only displays it as a black-and-white outline. Every other font available in Google Sheets does not have support for emojis.

If Google Sheets allowed the addition of custom fonts, this would be fixable. However, it is currently not possible to add custom fonts into Google Sheets. There are additional "advanced" fonts hidden in the "Fonts Dropdown->More Fonts" menu (one of which is Noto Emoji), but adding truly custom fonts is not possible.

Perhaps a correctly built extension could fix this issue. Currently though, no such extension seems to exist. One Google Sheets add-on called Free and New Font purports to add this functionality, but it has absolutely abysmal reviews and demands some very dodgy permissions regarding one's Google account. I have not tested it, and have intentionally refrained from linking to it because it seems nearer to a virus than actual solution. I would not want anyone skimming this to get the wrong idea.

All of that leaves us with no way (except Noto Emoji) to have Google Sheets recognize emoji; therefore, there is no way to use the built-in Google Sheets "Export as PDF" functionality and have emoji be exported correctly.

Alternatives

Since it is not possible to complete this task directly, I have provided some alternatives below.

Use Noto Emoji

Noto Emoji itself is the closest option Google Sheets has for exporting to .pdf which include emoji. The downside of this is that Noto Emoji only exports emoji as black-and-white outlines. If this is serviceable, Noto Emoji can be accessed by:

1. Selecting all cells with emoji in them.
2. Clicking the fonts dropdown menu.
3. Selecting "More Fonts" at the top of the list.
4. Searching for "Noto Emoji" and selecting it.
5. Pressing "OK"

Export Using Another Method

As OSM suggests, exporting as an .html document instead of a .pdf would retain colored emoji correctly. Another program can then be used to convert the .html document into a .pdf. Similarly (with even more jankiness), a screenshot of the document could be taken, and the image added to a PDF later. If access to Excel is possible, Google Sheets supports exporting to .xlsx, and Excel has better support for exporting emojis into PDFs.

The downside of this solution is that it requires numerous additional steps that take place outside of Google Sheets. If this is serviceable, refer to OSM's answer for steps to implement the .html solution, or Microsoft documentation for steps to implement the .xlsx solution.

Use Images Instead

Rather than using emoji, images could instead be used as this answer by pjmg suggests. Using the =IMAGE() formula (documentation here), a picture of an emoji could be pulled from an outside source. Google Sheets would correctly export that image when a .pdf file is created.

The downside of this solution is that it is cumbersome to find sources for external images, the images are not true emoji (which will be evident when editing the document), and if the host of the image goes down, exporting will no longer function correctly. If this is serviceable:

1. Find a website that hosts images of emoji. In this answer, jpmg suggests iEmoji. The Emojipedia website would also work.
2. Right click the image and click "Open in New Tab".
3. Copy the link and put it into an =IMAGE() formula in the desired cell.

For example (courtesy of jpmg), if you wanted the "face with tears of joy" emoji, you could use =IMAGE("https://s3.amazonaws.com/pix.iemoji.com/images/emoji/apple/ios-12/256/face-with-tears-of-joy.png") or =IMAGE("https://emojipedia-us.s3.dualstack.us-west-1.amazonaws.com/thumbs/120/apple/325/face-with-tears-of-joy_1f602.png")

Create New Functionality

If none of the above solutions are serviceable, it's possible to request that Google update their software to fix the problem. Max Makhrov has already submitted a bug report here, which can be voted on to improve the chances that Google updates things. Though, it should be noted: I think that this is technically all intended functionality, so a feature request might be a better route.

Furthermore, if anyone is particularly motivated, creating a non-shady alternative to the add-on I previously mentioned could also be a solution. Ultimately though, I suspect this would be a very time-consuming task for little gain.

Conclusion

It is not possible to use the built-in "Export as PDF" functionality in Google Sheets to get a resulting file with colored emoji. This is because there are no appropriate fonts available in Google Sheets. Workarounds exist, but none are optimal.

Answer 7

I've reported this as an issue here: https://issuetracker.google.com/issues/241147770

Please wote the issue if you want Google to solve it.

---

Note: when I use fonr Noto Emoji emojis are printed, but they are not colored.

To use this font select 'More Fonts' option.

Answer 8

Running into the same problem, and so far the only work-around I have discovered is to go to File > Download > Web Page and then print the resulting HTML file for that sheet. You have to check the option in your browser's print dialogue to print background and images to get some of the formatting to print correctly.

Answer 9

Emojis are considered to be special characters.

So according to this, if you want to insert a special character, it is recommended you follow the steps mentioned in there.

As for the reason why the PDF is not exported in the way you expect it - this is due to encoding since emojis are special characters. I suggest you insert the emoji as an image and only afterwards export the spreadsheet.