skip to Main Content

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

google sheet layout

Here is the lookup table layout

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

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:

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


  1. Chosen as BEST ANSWER

    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

    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 EDIT: this is the problem variable in question - do not use
              x = i + 2; // this is the correct index we want
              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(x + 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
        }
      }
    }
    



    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


  2. I think if you adjust rowIndex = j + 2; like this then this comparison if (rowIndex !== -1) { must change to if(rowIndex !== 1 but without understanding what you are trying to accomplish I can’t take it any further.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search