Skip to content

Instantly share code, notes, and snippets.

@hamletbatista
Created November 9, 2017 21:54
Show Gist options
  • Save hamletbatista/c1c5b6996c9c6373bcf445188944aa38 to your computer and use it in GitHub Desktop.
Save hamletbatista/c1c5b6996c9c6373bcf445188944aa38 to your computer and use it in GitHub Desktop.
A custom Google Sheets function that compares two data sets with a pair of related values, one textual and one numeric
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)
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment