I have a google sheet with multiple columns. I wish to combine values of each row into an string and convert that string to base64.
example: Sheet Data
A1 B1 C1 D1 E1
NewYork Smith kalk [email protected] 468783778
String:
NewYork,Smith,kalk,[email protected],468783778
Resultant base64:
TmV3WW9yayxTbWl0aCxrYWxrLHNtaXRoQGdtYWlsLmNvbSw0Njg3ODM3Nzg=
I joined all the column data into one
NewYork,Smith,Kal T,kalk,[email protected],468783778
by using this formula:
=arrayformula(A2:A&", "&B2:B&", "&C2:C&", "&D2:D&", "&E2:E)
Then, I opened script editor (Tools ->Script editor) and write the following function BASE64() to it. Apply Base64() function to column in which above data is present and it will do the encoding.
/**
* A custom function that encodes or decodes base64.
*
*@param {"R29vZ2xl"} data The string to encode/decode.
*@param {1} encode 1/true = encode (default).0/false = decode.
*@param {1} charsetStr The character set to use. Allowed values are "UTF-8" and "US-ASCII". Defaults to UTF-8.
*@param {0} websafe Whether the output string should be safe for use in URLs or not. Defaults to false.
*@param {0} asString Whether the decoded value should be returned as a string (default) or byte array.
*@result {"Google"} The result to be returned.
*@customfunction
*/
function BASE64(data,encode,charsetStr,websafe,asString) {
if(data==="" || data==null){return "No data"}
if(encode==="" || encode==null){encode=1}
else if(encode != 1 && encode!=0 && encode!= true && encode!= false){return "Encode?";}
if(encode==true){encode=1;}
else if(encode==false){encode=0;}
if(charsetStr==="" || charsetStr==null){charsetStr="UTF-8"}
else if(charsetStr!="UTF-8" && charsetStr!="US-ASCII"){return "Charset?"}
if(charsetStr=="UTF-8" || charsetStr==1){var charset = Utilities.Charset.UTF_8;}
else{var charset = Utilities.Charset.US_ASCII;}
if(websafe==="" ||websafe==null){websafe=0}
else if(websafe != 1 && websafe!=0 && websafe!= true && websafe!= false){return "Websafe?";}
else if(websafe==true){websafe=1;}
else if(websafe==false){websafe=0}
if(asString==="" ||asString==null){asString=1}
else if(asString != 1 && asString!=0 && asString!= true && asString!= false){return "AsString?";}
else if(asString==true){asString=1;}
else if(asString==false){asString=0}
var value;
if(encode==0){
if(websafe==0){
if(asString==0){
value= Utilities.base64Decode(data, charset);
}else{
value= Utilities.newBlob(Utilities.base64Decode(data, charset)).getDataAsString(charsetStr);
}
}else{
if(asString==0){
value= Utilities.base64DecodeWebSafe(data, charset);
}else{
value= Utilities.newBlob(Utilities.base64Decode(data, charset)).getDataAsString(charsetStr);
}
}
}
else{
if(websafe==0){
value= Utilities.base64Encode(data, charset);
}
else{value= Utilities.base64EncodeWebSafe(data, charset);}
}
return value;
}
