I have 2 columns A and B that contain Country and State/City. This is a spreadsheet called Data, I have another spreadsheet called Catalog that contains the Country and City along side different data on the following columns. I want to search the combination of both A column and B column and obtain C value of Catalog
I could have data like:
A | B | C | D
USA | Washington DC | 23423 | DC
USA | Maryland | 23421 | MD
I want to get C or D from the catalog spreadsheet
In the data catalog I would have:
A | B | C
USA | Washington DC | Run Function(A1:B1, Catalog!A2:B222)
USA | Maryland | Run Function(A2:B2, Catalog!A2:B222)
Using Bard, I've gotten this function:
function searchCatalog(rangeOfData, rangeOfCatalog) {
const spreadsheet = SpreadsheetApp.getActive();
// Get the values in the range of data.
var dataValues = spreadsheet.getRange(rangeOfData).getValues();
// Get the values in the range of catalog.
var catalogValues = spreadsheet.getRange(rangeOfCatalog).getValues();
// Iterate over the rows in the catalog.
for (var i = 0; i < catalogValues.length; i++) {
// If the data matches the catalog, return the code.
if (dataValues[0] == catalogValues[i][0] && dataValues[1] == catalogValues[i][1]) {
return catalogValues[i][2];
}
}
// If the data does not match the catalog, return None.
return 'None';
}
I keep getting Range not found
In your showing script and your 2 tables and your question, if Run Function(A1:B1, Catalog!A2:B222) is searchCatalog(A1:B1, Catalog!A2:B222), the values of A1:B1 and Catalog!A2:B222 are given as the 2-dimensional arrays. But, your script uses them as the range. I guessed that this might be the reason for your current issue of I keep getting Range not found.
And, in your expected result, you want to retrieve the value of column "C" of "Catalog" sheet. But, your custom function uses only columns "A" and "B". I think that this might be your 2nd issue.
When these points are reflected in your script, how about the following sample script?
function searchCatalog(rangeOfData, rangeOfCatalog) {
const values = rangeOfCatalog.find(([a, b]) => [a, b].join("") == rangeOfData[0].join(""));
return values ? values[2] : "None";
}
In this case, please put a custom function of =searchCatalog(A1:B1, Catalog!A2:C222) into a cell. By this, this script is run and the value is returned. When your showing 2 tables are used, =searchCatalog(A1:B1, Catalog!A2:C222) returns 23423.
If you want to use your showing script, in this case, it is required to give the vlues as the string like =searchCatalog("A1:B1", "Catalog!A2:C222"). Please modify it as follows.
function searchCatalog(rangeOfData, rangeOfCatalog) {
const spreadsheet = SpreadsheetApp.getActive();
// Get the values in the range of data.
var dataValues = spreadsheet.getRange(rangeOfData).getValues()[0]; // Modified
// Get the values in the range of catalog.
var catalogValues = spreadsheet.getRange(rangeOfCatalog).getValues();
// Iterate over the rows in the catalog.
for (var i = 0; i < catalogValues.length; i++) {
// If the data matches the catalog, return the code.
if (dataValues[0] == catalogValues[i][0] && dataValues[1] == catalogValues[i][1]) {
return catalogValues[i][2];
}
}
// If the data does not match the catalog, return None.
return 'None';
}
As another pattern, when you want to retrieve the result values using one custom function, how about the following sample script? In this case, please put a custom function of =searchCatalog(A1:B, Catalog!A2:C222) into a cell. By this, the script is run.
function searchCatalog(rangeOfData, rangeOfCatalog) {
const obj = rangeOfData.map(r => r.join(""));
return rangeOfCatalog.map(([a, b, c]) => obj.includes([a, b].join("")) ? c : "None");
}