Encode google sheet rows into base64 string

Question

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=

What did you try so far? Please add your code and tell which difficulties you have with it or where you get errors. — Pᴇʜ, Aug 20 '18 at 10:11
Is this information useful for your situation? https://developers.google.com/apps-script/reference/utilities/utilities#base64Encode(String) — Tanaike, Aug 20 '18 at 22:33
@Tanaike Thank you for your help. Used the same function to solve the problem. — daemonThread, Aug 23 '18 at 12:11

score 6 · Accepted Answer · answered Aug 22 '18 at 06:49

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;  
    }

Encode google sheet rows into base64 string

1 Answers1

Linked