Script setValue based on the values of column based on value of other column matching reference

Question

https://docs.google.com/spreadsheets/d/1g5mLRcwBb9rLaJ4qIUy1PLaBNNfuwvXwct1cBr2QVgk/edit#gid=1271563337

basicly im trying to figure out a script that would:

• Find match in Relação!A:A to content of Config!B3
  • in my example ABEV3 it would be Relação!A3
  • that would find a correponding row 3
• so the matching cel in Relação!L:L would be Relação!L3
• update Relação!L3 with content of Config!D9

---

basic idea with part of code that i can do to try to ilustrate

function setSheetID()
{ 

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet_c = ss.getSheetByName('Config');

  var ticket = ss.getRange(3,2,1,1).getValues(); // Config!B3
  var sheetID = ss.getRange(9,4,1,1).getValues(); // Config!D9

  const sheet_r = ss.getSheetByName('Relação');

  var LR = sheet_r.getLastRow(); 
  var LC = sheet_r.getLastColumn();

  var row_to_match_with_ticket = sheet_r.getRange(1,1,LR,1).getValues(); // Relação!A:A

// that part i have no idea how to get range for target where ticket would match with row_to_match_with_ticket, so i will put the result as example

  for (var i = 0; i <= 1; i++) // i dont understand that, just setting exemple from posts ive seen
  {
    if (row_to_match_with_ticket[i] == ticket ) 
    {  
      var target = sheet_r.getRange(3,12,1,1);  // Relação!L3 // im harcoding result, cos i dont fully understant what i wrote in the example 

      sheet_r.getRange(target).setValues(sheetID);
    }
  } 

};

---

now i will try to ilustrate with a query: (im not in USA, so ; instead of ,)

• to find my target:

  query('Relação'!A:L; "SELECT L WHERE (A = '"&Config!B3&"') LIMIT 1";0)

• update that cell

---

• UPDATE WITH Config!D9 ( Relação!L:L WHERE Relação!A:A = Config!B3 )
• in this case update Relação!L3 WITH VALUES OF Config!D9

searched and results where too complex to me understand and be able to try to adapt to my needs

thanks in advance

Edit: tried to simplify, to make it easier to understand as im terrible to explain aparently, but that script would run on multiple SS that i quite often replace as i improve it, so i need each sheet to update a main sheet

Edit 2: tried to provide a logical way to understant the inputs and outputs, its might be easier to see in the sheet i provided as its comented and colored, tried to describe the best possible way i could

Edit 3: added basic code to try to show what i want to do

Answer 1

I believe your goal is as follows.

• You want to retrieve the value of cell "B3" of "Config" sheet.
• You want to search the retrieved value from the column "A" of "Relação" sheet.
• When the value is found, you want to put the value of cell "D9" of "Config" sheet to the column "L" of the same row of the searched value.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = ["Config", "Relação"].map(s => ss.getSheetByName(s));
  const [b3, d9] = ["B3", "D9"].map(r => src.getRange(r).getValue());
  const search = dst.getRange("A2:A" + dst.getLastRow()).createTextFinder(b3).findNext();
  if (!search) return;
  search.offset(0, 11).setValue(d9);
}

• In this answer, the value is searched using TextFinder.

Reference:

• createTextFinder(findText)