2

In Google Sheets, is there a way to calculate the width, in pixels, of a piece of text given a font and a font size? Excel has a PixelWidth function, but I didn't find any equivalent function or script for Sheets.

My goal is to be able to compare lengths of translated sentences in a UI. For example, if the English sentence is "Hi", and the French translation is "Bonjour", I would like to calculate how much more space the translation will be using. The length of the string is not enough since fonts have different character widths. I would like to calculate it directly in Sheets to give translators an indication of if the sentence can fit in the UI element.

Gilles-Philippe Paillé
  • 3,195
  • 1
  • 8
  • 21
  • 1
    Related: https://stackoverflow.com/questions/53218561/how-do-you-tell-whether-text-fits-within-its-bounding-shape-using-google-slides – ziganotschka Aug 17 '20 at 15:03
  • @ziganotschka Thanks for the link. It is similar but I doubt that an answer to this question will solve my problem. So I would not suggest closing my question based on the fact that the linked question exists. – Gilles-Philippe Paillé Aug 17 '20 at 15:47
  • @Gilles-PhilippePaillé - that's why there are no close votes on your question. Also, that question does not have an upvoted or accepted answer to be used as dupe target in the first place. Could you make it a bit less broad - what is the context where you want to get the width? – Oleg Valter is with Ukraine Aug 17 '20 at 16:19
  • @OlegValter Sure! I added a paragraph explaining the context of my question. Hopefully, there is another way to achieve my goal. – Gilles-Philippe Paillé Aug 17 '20 at 17:26
  • 2
    @Gilles-PhilippePaillé - thanks, so I assume the UI you are talking about is not a custom dialog / prompt, etc.? If you don't care about how the spreadsheet looks like, you can `autoResizeColumn` > then `getWidth` to get width in points (+you will have to pixels). Other than that I think you may have to do a complex calc of actual width... – Oleg Valter is with Ukraine Aug 17 '20 at 18:17
  • @OlegValter Thanks for the suggestion! Since sentences are in a column, I would have to copy the value to an temporary column before running the resize-getwidth script, for all sentences. I suppose it could be done, although would be very time consuming. I was hoping that Sheets provide such functionality, either native or through a add-on. – Gilles-Philippe Paillé Aug 17 '20 at 19:28
  • 2
    The sample script of this thread might be close to @Oleg Valter 's proposal. https://stackoverflow.com/a/62709196 – Tanaike Aug 17 '20 at 22:14
  • 1
    @Gilles-PhilippePaillé - that depends on how many sentences you expect, yes, I think it might be slow on a bigger dataset... You could use well-known width/height ratios of fonts to approximate the width as well. Unfortunately, everything width/height-related is notoriously hard to work with in GAS. – Oleg Valter is with Ukraine Aug 18 '20 at 00:49
  • 1
    @OlegValter I expect around 500 sentences per language and there are 13 languages for now. I think the temporary column might be too long to consider this as an option. I'm thinking about using another tool to create a table of width for each character, then use it in a script. Thanks a lot for your input and suggestions! – Gilles-Philippe Paillé Aug 18 '20 at 14:51
  • Well, that is quite a few rows to process. Yes, this will be slowish, although I may suggest transposing rows into a temp sheet with columns equal to a certain number of rows, then shifting, repeat until done - that will be relatively fast. That said, an external tool will probably be more effective (also you can call this external service with `UrlFetchApp` and use the spreadsheet as a database of sorts). Anyways, do keep an eye on the question, as I will likely take a shot at implementing what the suggestion above – Oleg Valter is with Ukraine Aug 18 '20 at 15:17

1 Answers1

-1

Technically, Not possible for a rule of thumb when it comes to pixel width measurement of text.

I had been working on this problem and learned this is impossible because Pixel widths are different depending on the screen resolution. The higher the resolution, the more pixels there are.

Example: A High Definition TV with 1080p resolution is composed of two million pixels (1920 x 1080), while a 4K TV (aka Ultra High Definition) has over eight million pixels (3840 x 2160)(source).

So there would be no uniform pixel width measurement to go by in general. So I defaulted to using an average pixel width from 4 sites that measure for SERPs and then continue to iterate the value lengths as we launch websites using the pixel counter to try to avoid truncated search results.

Daine Dvorak
  • 1