How do can I use the setvalue() in a foreach() loop?[Google sheet Script]-CodePudding

It's my first post and I am a newbee in google sheets script programing.

I want to my script when I run it, to go first through a list of clients(row[0]). when the client has been found, test for the row[2] if it is a "yes" or "no" string object and when it is a "yes" option, rewrite it as a "no".

another question that i have is how can I know which ligne (row) i am testing when i am using the foreach function.

function cleanpassagepage(NomClient)
{
  const passageclientsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("relevé chez client");
  const numlastrowIS = passageclientsheet.getLastRow();
  const dataIS = passageclientsheet.getRange(2,1,numlastrowIS,3).getValues();
  dataIS.forEach(function(row)
  {
    if (row[0]==NomClient)
    {
      if(row[2]=="non")
      {
        row[2]= setValue("oui");
      }
    }
  });
}

PS : the script doesn't give me an error to understand where did I messed up.

thank you

CodePudding user response：

Assuming you call this function only once the use of setValue() is okay. If you are calling this many times you should investigate using setValues() instead.

function cleanpassagepage(NomClient) {
  const passageclientsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("relevé chez client");
  const numlastrowIS = passageclientsheet.getLastRow();
  const dataIS = passageclientsheet.getRange(2,1,numlastrowIS,3).getValues();
  var i = 0;
  for( i=0; i<dataIS.length; i   ) {
    if( dataIS[i][0] === NomClient ) {
      if( dataIS[i][2] === "non" ) {
        passageclientsheet.getRange(i 1,3).setValue("oui");
      }
      return;
    }
  }
}

CodePudding user response：

setValue is for range

you can try this (I have also added the num of row)

function cleanpassagepage(NomClient)
{
  const passageclientsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("relevé chez client");
  const numlastrowIS = passageclientsheet.getLastRow();
  const dataIS = passageclientsheet.getRange(2,1,numlastrowIS,3).getValues();
  var lignes=[]
  dataIS.forEach(function(row,i)
  {
    if (row[0]==NomClient)
    {
      if(row[2]=="non")
      {
        row[2]= "oui"
        lignes.push(i 2)
      }
    }
  });
  passageclientsheet.getRange(2,1,numlastrowIS,3).setValues(dataIS)
  if (lignes.length>0) Browser.msgBox('Ligne(s) modifiée()s : ' lignes.join(','))
}

reference

setValue()