How to decode cloudflare email in Google sheets

Question

I'm just getting started with Google Appscript and here I'm trying to create a script to help me decode Cloudflare email.

I have the following Javascript code which I have fused with my other code to take the values from column A and upon converting, dispose them in Column B.

My code is throwing an error TypeError: sheetS.getRange(...).cfDecodeEmail is not a function

What I have:

function Decode() {

var sss = SpreadsheetApp.openById("1fDXv1L1YmXzbUXJbzGE6suc5HWToHlUuO-zBzVZDcX0");
var sheetS = sss.getSheetByName("Guide");
var AG1val = sheetS.getRange('A1:A').getValues(); // 


function cfDecodeEmail(encodedString) {
    var email = "", r = parseInt(encodedString.substr(0, 2), 16), n, i;
    for (n = 2; encodedString.length - n; n += 2){
        i = parseInt(encodedString.substr(n, 2), 16) ^ r;
        email += String.fromCharCode(i);
    }
    return email;
}

sheetS.getRange("B1:B").cfDecodeEmail(AG1val); // decode to B1:B
}

Here's the "untouched" Javascript code that purposely does what I am trying to do except that I am trying to do it inside Appscript.

function cfDecodeEmail(encodedString) {
    var email = "", r = parseInt(encodedString.substr(0, 2), 16), n, i;
    for (n = 2; encodedString.length - n; n += 2){
        i = parseInt(encodedString.substr(n, 2), 16) ^ r;
        email += String.fromCharCode(i);
    }
    return email;
}

console.log(cfDecodeEmail("543931142127353935313e352e7a373b39")); // usage

Thanks.

Answer 1

AG1val is a two dimensional array (see: https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues) and it seems you are trying to pass it into the function as string.

You should first loop through that array, pass each element of that array through the cfDecodeEMail function and then write these values to the B1:B range.

Something similar to below, did a quick test and this seems to work:

function decode() {
    var sss = SpreadsheetApp.openById("1fDXv1L1YmXzbUXJbzGE6suc5HWToHlUuO-zBzVZDcX0");
    var sheetS = sss.getSheetByName("Guide");
    var AG1val = sheetS.getRange('A1:A').getValues(); // 
  
  function cfDecodeEmail(encodedString) {
      var email = "", r = parseInt(encodedString.substr(0, 2), 16), n, i;
      for (n = 2; encodedString.length - n; n += 2){
          i = parseInt(encodedString.substr(n, 2), 16) ^ r;
          email += String.fromCharCode(i);
      }
      return email;
  }
  
  let results = AG1val.map( (row) =>{ //loop through each row
      let cellValue = row[0]; //since there is only one column, get it by the first index (0)
      if(cellValue !== ''){ //pass the value to the function if cell contains a value
        return [cfDecodeEmail(cellValue)]; //return it in an array to contain the two-dimensional array structure
      } else{
        return ['']; //otherwise return an empty string
      }
  })
  
  sheetS.getRange("B1:B").setValues(results) //call the setValues function to write back to the sheet
  
}