setValue() in several vélulas (.foreach ???)

Asked

Viewed 59 times

1

I use the function below to concatenate the value of the current cell in google Sheets and a link to create a function HIPERLINK() in the same cell.

function setCustomLink(){
      var ss = SpreadsheetApp.getActive().getActiveSheet();
      var cell = ss.getActiveCell();
      var cellValue = cell.getValue();
      cell.setValue('=HYPERLINK("https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&-H&Host:&projudi.tjpr.jus.br&-H&User-Agent:&Mozilla/5.0&(Windows&NT&6.3;&WOW64;&rv:49.0)&Gecko/20100101&Firefox/49.0&-H&Accept:&text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8&-H&Accept-Language:&pt-BR,pt;q=0.8,en-US;q=0.5,en;q=0.3&--compressed&-H&Referer:&https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=iniciarSimples&-H&Cookie:&projudiContCookie=0;&JSESSIONID=053165f8dd5f8532c326f3eb06d7;&projudi-route=4;&dtLatC=54;&dtPC=-;&dtCookie=49542FA50EF89B032E8685F08394F120|UHJvanVkaSstK0V4dGVybm98MQ&-H&Connection:&keep-alive&-H&Upgrade-Insecure-Requests:&1&--data&page=1&flagNumeroUnico=true&flagNumeroFisicoAntigo=false&numeroProcesso='
      + cellValue + '";"' + cellValue+'")');
 }

The function works super well, the problem is that I need to do one by one. Normally I have to perform the function several times in a table with all numbers in the same column (A2:A):

exemplo

Is there any way to do a function that does all the selected cells at once? I tried .foreach(function(r){return r[0]}), but I could never make it work right. Thank you!

(As a matter of interest: column A has numbers of processes that I along with the search link of the TJPR Projudi system to access the file of each process).

1 answer

0


For those who still have doubts, that was the answer I got in the US stack overflow and it worked:

   // Use a global constant to ease editing the value should the URL need to be changed.
    var URL_PREFIX = 'http://url/?param1=value1&param2=';

    function setCustomLinks() {
      var activeSheet = SpreadsheetApp.getActiveSheet();
      var ranges = activeSheet.getActiveRangeList().getRanges();

      // Loop through the ranges.
      for (var i = 0; i < ranges.length; i++) {

        // Get all cell values of the range into a 2D array.
        var values = ranges[i].getValues();

        // Loop through the 2D array of values.
        for (var j = 0; j < values.length; j++) {
          for (var k = 0; k < values[j].length; k++) {

            // Replace each value with a hyperlink formula.
            values[j][k] = createCustomLink(values[j][k]);
          }
        }

        // Set the cell values using the new values.
        ranges[i].setValues(values);
      }
    }

    function createCustomLink(value) {
      var url = URL_PREFIX + value;
      return '=HYPERLINK("' + url + '", "' + value + '")';
    }

Browser other questions tagged

You are not signed in. Login or sign up in order to post.