text to hex then to base64

[Ahmed General]

Hello everyone, I need help finding a function in Google Sheet or a script that converts text to hex and then converts hex to base64

[Tanaike]

Can you provide the sample input and output values you expect?

[Ahmed General]

https://docs.google.com/spreadsheets/d/1TDdIhkBhfPBmn7MtSVsPpbE1Lm5UABlyiCOpntafGLA/edit?usp=sharing

[Tanaike]

Thank you for providing the sample values. In that case, how about the following script? Please copy and paste the following script to the script editor of Spreadsheet and save the script.

const SAMPLE = v => Utilities.base64EncodeWebSafe(v.flatMap(r => r.flatMap(c => Utilities.newBlob(c).getBytes())));

When you use this script, please put a custom formula of `=SAMPLE(ARRAYFORMULA(TO_TEXT(A2:A6)))` to a cell. In this case, it is required to give the values as the string type. So ARRAYFORMULA and TO_TEXT are used.

When this script is used for your sample Spreadsheet, when `=SAMPLE(ARRAYFORMULA(TO_TEXT(A2:A6)))` is put to a cell, "Qm9icyBSZWNvcmRzMzEwMTIyMzkzNTAwMDAzMjAyMi0wNC0yNVQxNTozMDowMFoxMDAwLjAwMTUwLjAw" is obtained.

[Tanaike]

If you are not required to use `base64EncodeWebSafe`, you can use the following script.

const SAMPLE = v => Utilities.base64Encode(v.flatMap(r => r.flatMap(c => Utilities.newBlob(c).getBytes())));

[Ahmed General]

Thank you, but I want a code to convert from text to hex and another code to convert from hex to base64

[Tanaike]

Thank you for replying. At first, I have to apologize for my poor English skill. I couldn't notice that you wanted the following 2 scripts.

1. Convert string to hex
2. Convert hex to base64

From your replying, I added 2 scripts for achieving your goal. Could you pleased confirm it? And, I would be grateful if you can forgive my poor English skill.

### Convert string to hex

const SAMPLE1 = v => v.map(r => r.flatMap(c => Utilities.newBlob(c).getBytes()).map(byte => ('0' + (byte & 0xFF).toString(16)).slice(-2)).join('')).join('');

### Convert hex to base64

const SAMPLE2 = v => {
  const r = [];
  for (let i = 0; i < v.length; i += 2) r.push(v.substr(i, 2));
  const rr = r.map(e => parseInt(e[0], 16).toString(2).length == 4 ? parseInt(e, 16) - 256: parseInt(e, 16));
  return Utilities.base64Encode(rr);
}

### Testing
When the following values are put to the cells "A2:A6",

Bobs Records
310122393500003
2022-04-25T15:30:00Z
1000.00
150.00

please put a formula of "=SAMPLE1(ARRAYFORMULA(TO_TEXT(C2:C6)))" to a cell "E2". By this, "426f6273205265636f726473333130313232333933353030303033323032322d30342d32355431353a33303a30305a313030302e30303135302e3030" is obtained.

When a formula of "=SAMPLE2(E2)" to a cell "F2", "Qm9icyBSZWNvcmRzMzEwMTIyMzkzNTAwMDAzMjAyMi0wNC0yNVQxNTozMDowMFoxMDAwLjAwMTUwLjAw" is obtained.

[Ahmed General]

Thank you, I benefited a lot, but when converting the text to Hex, I want to convert every cell on my own, and I will merge them later.

[Ahmed General]

up

[Kamil Jarosz]

Hi there, I am currently trying to use this formula for my use case and it very useful, however is there any way to do execute this script as a regular formula and not an array formula? In other words i would like to convert only one cell at a time to UTF-8 and drag this down the sheet to repeat action. This is because each row contains different data which I would like encoded in the corresponging seperate column on the same row, not an array of all rows compiled into one cell.

[Kareem Elkafi]

hello all, i hope you are doing well..

anyone have any update about this issue .. i want make same thing by apps script to google sheet

if anyone still here we can contact by whatsapp to talk about it