google-apps-script - Problems to get only the value of a row within a 2D array Google Apps Script

Question

In my sheet I have the range E10:E34 which contains URLS.

I also have the range C10:C34 which contains the references.

In the range I10:I34 the following formula should go:

=IF(SEARCH("URL";URL cell A1notation);"reference";"")

This formula, converted to real data, would look like this;

=IF(SEARCH("https://collaresmolones.com/wp-content/uploads/2021/01/Mi-vecino-Totoro.jpg";E10);"322";"")

I am working with Google Apps Script so that it converts the formula automatically with the data of each row, that is, with the URL, the A1notation cell of the URL and the value of the reference.

This is my code so far:

function ref_URL_formula3(){

  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hoja = libro.getSheetByName("ImportXML");

  var rangoURLvalor = hoja.getRange('E10:E34').getValues();
  var colURL = 'E';
  var rangoURLnotationCelda = hoja.getRange('E10:E34').getRow();
  var rangoREFvalor = hoja.getRange('C10:C34').getDisplayValues();
  var rangoFormula = hoja.getRange('I10:I34');

  rangoURLvalor.flat().forEach((v, i) => {
      if (v == rangoURLvalor[i][0]) rangoFormula.setFormula('=IF(SEARCH("'+ rangoURLvalor +'";'+ colURL+rangoURLnotationCelda +');"'+ rangoREFvalor +'";"")')
    })
 }

Inside the formula, in the URL and in the Reference it does not return the unique value of the row, otherwise i get the all values ​​of the entire range.

Specifically, in the cells of the range I10:I34 this is what I get:

=IF(SEARCH("https://collaresmolones.com/wp-content/uploads/2021/01/Mi-vecino-Totoro.jpg,https://collaresmolones.com/wp-content/uploads/2021/01/Kodama-Princesa-Mononoke.jpg,https://collaresmolones.com/wp-content/uploads/2021/01/Coco-and-friends.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Dragon-Ball.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/El-Rey-Leon.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/El-Rey-Leon-2.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/El-Rey-Leon-3.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/El-Rey-Leon-4.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Flamingo.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Harry-Potter-1.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Harry-Potter-2.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Harry-Potter-Snitch-Dorada.jpg,https://collaresmolones.com/wp-content/uploads/2021/01/Harry-Potter-2.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Harry-Potter-Griffindor.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Minnie.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Star-Wars-2.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Supercats-Hero.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Vis-a-vis-2.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Super-mario.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Gat-y-gos-Gato-y-perro-CatDog.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Rugrats.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Kimetsu-no-Yaiba.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Inuyasha.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Fire.jpg,https://collaresmolones.com/wp-content/uploads/2020/10/Blue.jpg";E10);"322,323,321,301,303,302,304,319,305,306,307,315,324,308,300,309,311,317,310,316,312,313,314,318,320";"")

When what I want to get is this other:

=IF(SEARCH("https://collaresmolones.com/wp-content/uploads/2021/01/Mi-vecino-Totoro.jpg";E10);"322";"")

It is clear that there is an error in the path of the rows, however, I really do not know what I am doing wrong.

I am a newbie to Google Sheets and Google Apps Script, so all help is well appreciated.

Thank you.

标签： google-apps-scriptgoogle-sheets

Answer 1

You can arrange all formulas in a map function and put them in a range by using setFormulas() function.

function ref_URL_formula3(){

  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var hoja = libro.getActiveSheet();

  var rangoURLvalor = hoja.getRange('E10:E34').getValues();
  var rangoREFvalor = hoja.getRange('C10:C34').getValues();
  var rangoFormula = hoja.getRange('I10:I34');

  var formulas = rangoURLvalor.map(function (v,i) {

    return ['IF(SEARCH("' + v + '"; E' + (i + 10) + ');' + rangoREFvalor[i] +';"")']

  })

  rangoFormula.setFormulas(formulas) 

}