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])
}
}
In your showing script, how about the following modification?
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() || ""));
.
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);
}