I am trying to set the value of a cell in a column when two other columns at an index match values. How can I set the value using an index? (<Edited)

  for (let i = 0; i < assetId.length; i  ) {
    for (let p = 0; p < oldId.length; p  ) {
      if (assetId[i] !="" && oldId[p] !="") {
        if (assetId[i] == oldId[p]) {
          Logger.log('Old Match: '   assetId[i])

          //if match modify 4th column at row [i] to 'null'
      
     d.getRange(i,3).setValue('null')
        }
      }
    }
 }

Based on if assetId[i] == oldId[p], I am trying to change column F of row [i] to 'null'

Edit (examples requested)

Column J is oldId and K is newId

EXPECTED OUTPUT: F4 should be null

Full code:

function replaceIds() {

   const ss = SpreadsheetApp.getActiveSpreadsheet()
   const r = ss.getSheetByName("Form Responses 1")
   const d = ss.getSheetByName("Devices")

   const oldId = r.getRange("J2:J").getValues().flat()
   const newId = r.getRange("K2:K").getValues().flat()
   const studentName = r.getRange("C2:C").getValues().flat()

   const assetId = d.getRange("G3:G").getValues().flat()
   const annotatedUser = d.getRange("E3:E").getValues().flat()


   for (let i = 0; i < assetId.length; i  ) {
     for (let p = 0; p < oldId.length; p  ) {
       if (assetId[i] !="" && oldId[p] !="") {
         if (assetId[i] == oldId[p]) {
           Logger.log('Old Match: '   assetId[i])
           //if match modify 4th column at row [i] to 'null'
           d.getRange(i,3).setValue('null')
         }
       }
    }

    //new asset ID loop
    for (let r = 0; r < newId.length; r  ) {
      //Logger.log(oldId[p])
      if (assetId[i] !="") {
        if (newId[r] !="") {
          //Logger.log('## not null ##')
          if (assetId[i] == newId[r]) {
            Logger.log('New Match: '   assetId[i])
          }
        }
      }
    }
  }
}

CodePudding user response：

Issue:

• Issue is that, using a nested for loop is not a good idea as you can't properly follow where the proper index is, and it will also needlessly reiterate on items that were already visited.

Solution:

• Looping only on the assetId should suffice, then using indexOf as it will help you identify if a certain element (current assetId) belongs in an array (list of oldIds).
• If assetId is found, indexOf will return a non-negative number (which is what index the element is found in the array).
• Exclude empty assetIds due to how you get your data
• Then you can remove the column of that same row, but since index starts at 0 and your data starts at 3rd row, we need to offset the getRange row so it would match the cell we want to delete properly.

Modifying your current solution, this is what the solution says above, and should work.

Script:

function replaceIds() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const r = ss.getSheetByName("Form Responses 1")
  const d = ss.getSheetByName("Devices")

  const oldId = r.getRange("J2:J").getValues().flat()
  const newId = r.getRange("K2:K").getValues().flat()
  const studentName = r.getRange("C2:C").getValues().flat()

  const assetId = d.getRange("G3:G").getValues().flat()
  const annotatedUser = d.getRange("E3:E").getValues().flat()

  // loop your assetId
  assetId.forEach(function(cell, index){
    // if assetId is listed under oldId, remove annotated location of that row
    // also, skip any rows where assetIds are blank
    if(oldId.indexOf(cell) > -1 && cell != "")
      // offset here is 3 since assetId starts at G3 and index starts at 0
      // 3 - 0 = 3, which is the offset, and 6 is column F
      d.getRange(index   3, 6).setValue('');
  });
}

Output:

CodePudding user response：

This function will change the value in column1 if the value of col2 at that index is in column 10 on any line. you can change the indices as you desire.

function findDataBasedOnMatch() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const sr = 2;//data start row
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).getValues();
  const col10 =vs.map(r => r[9]);//you pick the indices
  vs.forEach((r,i) => {
    if(~col10.indexOf(r[1])) {//you pick the indices
      sh.getRange(i   sr, 1).setValue('');
    }
  });
}