base64google-sheets-apigoogle-sheets-macros

Encode google sheet rows into base64 string


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       smith@gmail.com          468783778

String:

NewYork,Smith,kalk,smith@gmail.com,468783778

Resultant base64:

TmV3WW9yayxTbWl0aCxrYWxrLHNtaXRoQGdtYWlsLmNvbSw0Njg3ODM3Nzg=


Solution

  • I joined all the column data into one

    NewYork,Smith,Kal T,kalk,smith@gmail.com,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;  
        }