A custom Google Sheets function that compares two data sets with a pair of related values, one textual and one numeric

rangediff

function is_valid_range_(r) {
  return (r.length > 0 && r[0].length === 2);
}

function get_range_values_(r) {
  var values = []
  for(var i=0;i<r.length;i++) {
    values.push({ text: r[i][0].trim(), count: r[i][1] });
  }
  return values;
}

/**
 * A custom function that compares two data sets with a pair of related values, one textual and one numeric.
 * For example: page, and its traffic.
 *
 * @param {Sheet1!A1:B20} range1 Unique text1, count of text 1.
 * @param {Sheet2!A1:B20} range2 Unique text2, count of text 2.
 * @param {-1} flag = -1 if count of text 1 minus count of text 2 is less than zero, 0 if it is zero, and 1 if it is greater than zero
 * @returns Range of values as mentioned in the flag
 * @customfunction
 */

function RANGEDIFF(range1, range2, flag) {  
  if(!is_valid_range_(range1) || !is_valid_range_(range2)) { return "Error - invalid input ranges" }
  var r1 = get_range_values_(range1);
  var r2 = get_range_values_(range2);
  var retval = []
  
  r1_text_to_count_map = {}
  r1_text_found_in_r2 = {}
  for(var i=0;i<r1.length;i++) {
    r1_text_to_count_map[r1[i].text] = r1[i].count
    r1_text_found_in_r2[r1[i].text] = false;
  }
  
  var positives = [], negatives=[], zeroes = [], new_items=[], lost_items=[];
  
  for(var i=0;i<r2.length;i++) {
    var current_text = r2[i].text
    r1_text_found_in_r2[current_text]=true;
    related_r1_count = 0;
    related_r1_count = r1_text_to_count_map[current_text] || 0
    
    res = r2[i].count-related_r1_count
    var val = [current_text,res]
    if(!current_text || !current_text.length) {
      continue;
    }
    if(typeof(r1_text_to_count_map[current_text])!=="undefined") {
      if(res > 0) {
        positives.push(val);
      } else if (res < 0) {
        negatives.push(val);
      } else {
        zeroes.push(val);
      }
    } else {
      new_items.push(val);
    }    
  }

  for(var k in r1_text_found_in_r2) {
    if(!r1_text_found_in_r2[k]) {
      lost_items.push([k, -r1_text_to_count_map[k]])
    }
  }
  
  if(flag===-1) {
    retval = negatives ;
  }
  else if(flag===1) {
    retval = positives;
  }
  else if(flag===0) {
    retval = zeroes;
  }
  else if(flag===2) {
    retval = new_items;
  }
  else if(flag===-2) {
    retval = lost_items;
  }
  
  if(retval.length > 0) {
    return retval;
  } else {
    return ""
  }
}

function test_() {
  var sheet = SpreadsheetApp.getActiveSheet();
  RANGEDIFF(sheet.getRange("A17:B20").getValues(),sheet.getRange("G17:H21").getValues(),1)
}