I have a google sheet that i am trying to search, match, and then auto-populate some data to.
Here is the google sheet layout
Here is the lookup table layout
I wrote some javascript code to try to search cell A2 for a keyword, match it to a value in the lookup table, grab the corresponding data, and then populate it 1 row down, and 1 column right of the keyword. It should repeat this checking every 7th cell in column A until there are no more. Currently, my code works for the first keyword, and then inputs the data incorrectly for the remaining keywords.
function FindItemAndPopulate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Item Autofill");
var keywordRange = sheet.getRange("A2:A");
var keywords = keywordRange.getValues();
var lastRowA = sheet.getLastRow();
for (var i = 0; i < keywords.length; i += 7) { // Increment by 7 to check every 7th row
var keyword = keywords[i][0];
if (keyword !== "") {
var searchData = sheet.getRange("M2:M" + lastRowA).getValues();
var rowIndex = -1;
for (var j = 0; j < searchData.length; j++) {
if (searchData[j][0] === keyword) {
rowIndex = j + 2; // Adjust index for row offset due to starting from row 2
break;
}
}
if (rowIndex !== -1) {
var rowData = sheet.getRange(rowIndex, 14, 1, 5).getValues(); // Get data from columns N to R
var valuesToPopulate = [];
for (var k = 0; k < rowData[0].length; k++) {
valuesToPopulate.push([rowData[0][k] !== "" ? rowData[0][k] : ""]);
}
sheet.getRange(rowIndex + 1, 2, 1, 5).setValues([valuesToPopulate]); // Populate data into the next row and 1 column right
} else {
sheet.getRange("B" + (i + 2) + ":F" + (i + 2)).clearContent(); // Clear corresponding row if no match found
}
} else {
sheet.getRange("B" + (i + 2) + ":F" + (i + 2)).clearContent(); // Clear corresponding row if keyword is empty
}
}
}
I believe I made a mistake somewhere with the index of the keyword not being in the correct scope or not being properly updated, but I cannot figure out where. Any help would be appreciated.
Here is a photo of the result
of the code and a video of code execution
Edit as requested:
Here is the [link to the google document]|Removed since it is not needed anymore| Note: The relevant page is titled "Item Autofill"
here is the photo of intended output:
with the part that is relevant highlighted (the rest is handled elsewhere) and a video of me manually doing what the code should do
I also included a tab called Expected Output that shows what the table should look like after the code has ran- the red text is the part that the code i have posted about manages, the rest is handled elsewhere, so only focus on the red parts
2
Answers
I was able to find the problem by isolating it using console.log statements - the code was putting the data in rows with the row number coming from the keyword match location of the search table instead of the source table - i fixed it by adding the x variable and using that instead of rowIndex
Its an especially overlooked mistake because i have the correct logic in the clear statements (i+2) but for some reason decided to use rowIndex(j+2) for the actual data location ... I put the data in the wrong location but clear the actual location I wanted
I think if you adjust
rowIndex = j + 2;
like this then this comparisonif (rowIndex !== -1) {
must change toif(rowIndex !== 1
but without understanding what you are trying to accomplish I can’t take it any further.