Find which URLs are Listed in Column A and not in Column B via appscript

January 9, 2024

I have column A under which URLs are listed and column B under which the URLs are listed too. I want to find which URLs are listed in A but not in column B.

Code.gs

So far I was able to do this.

function populateColumnC() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1"); 
  var noOfRows = sheet.getLastRow()-1;
  const dataRange = sheet.getRange(2,1,noOfRows,2); 
  sheet.getRange(1,3,1,1).setValue('Listed in A but not in B');
  const dataValues = dataRange.getValues();
  //Logger.log(dataValues);


  for (var i =0; i<dataValues.length; i++) {

   Logger.log(dataValues[i])

  }

}

>Solution :

In your showing script, how about the following modification?

Modified script 1:

In this case, the output values are URLs.

function populateColumnC() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  var noOfRows = sheet.getLastRow() - 1;
  const dataRange = sheet.getRange(2, 1, noOfRows, 2);
  const dataValues = dataRange.getRichTextValues().map(r => r.map(c => c.getLinkUrl() || ""));
  // Logger.log(dataValues);

  const obj = new Set(dataValues.map(([, b]) => b));
  const res = dataValues.reduce((ar, [a]) => {
    if (!obj.has(a)) {
      ar.push([a]);
    }
    return ar;
  }, [['Listed in A but not in B']]);
  sheet.getRange(1, 3, res.length).setValues(res);
}

When this script is run, I guess that your expected result is put into column "C".
Although I’m not sure about your actual Spreadsheet, if const dataValues = dataRange.getValues(); returns the URLs, I think that const dataValues = dataRange.getValues(); can be also used instead of const dataValues = dataRange.getRichTextValues().map(r => r.map(c => c.getLinkUrl() || ""));.

Modified script 2:

In this case, the output values are the rich text including hyperlinks.

function populateColumnC() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  var noOfRows = sheet.getLastRow() - 1;
  const dataRange = sheet.getRange(2, 1, noOfRows, 2);
  const dataValues = dataRange.getRichTextValues();
  const obj = new Set(dataValues.map(([, b]) => b.getLinkUrl()));
  const res = dataValues.reduce((ar, [a]) => {
    const url = a.getLinkUrl();
    if (url && !obj.has(url)) {
      ar.push([a]); // or ar.push([a.copy().setLinkUrl(url).build()]);
    }
    return ar;
  }, [[SpreadsheetApp.newRichTextValue().setText('Listed in A but not in B').build()]]);
  sheet.getRange(1, 3, res.length).setRichTextValues(res);
}