javascriptgoogle-apps-scriptgoogle-workspace

Adding values of cells

Bonjour, i need to do basic operations on values in cells. For that i have developed a script but the values are added as in a string meaning that if a cell has the value 2 and the other one 3 it would return 23 instead of 5. I have tried parseInt but it returns #NUM.

I tried to retype the code using parseInt but it brings me a string instead of an integer. I can send the code if needed.

function Synthese() {
  const feuille = SpreadsheetApp.getActive().getSheetByName('Synthèse Opérations');
  const Quantite = 0;
  const Frais = 0;
  const Valeur = 0;
  const TotalAchat = 0;
  const TotalVersements = 0;

  feuille.activate;
  feuille.getRange('A2:I10000').clearContent();
  let j = 0;
  let k = 0;
  for (let i = 2; i <= 10000; i++) {
    if (feuille.getRange(i, 11).getValue() == "") {
      break
    }
    i = i + j;
    k = j;
    j = 0;
    if (feuille.getRange(i, 11).getValue() == feuille.getRange(i + j + 1, 11).getValue()) {
      let Quantite = parseInt(feuille.getRange(i, 15).getValue());
      let Frais = parseInt(feuille.getRange(i, 17).getValue());
      let Valeur = parseInt(feuille.getRange(i, 18).getValue());
      let TotalAchat = parseInt(feuille.getRange(i, 19).getValue());
      let TotalVersements = parseInt(feuille.getRange(i, 20).getValue());
      while (feuille.getRange(i + j, 11).getValue() == feuille.getRange(i + 1 + j, 11).getValue()) {
        feuille.getRange(i - k, 1).setValue(feuille.getRange(i + j + 1, 11).getValue());
        feuille.getRange(i - k, 2).setValue(feuille.getRange(i + j + 1, 12).getValue());
        feuille.getRange(i - k, 3).setValue(feuille.getRange(i + j + 1, 13).getValue());
        feuille.getRange(i - k, 4).setValue(feuille.getRange(i + j + 1, 14).getValue());

        Frais = Frais + parseInt(feuille.getRange(i + j + 1, 17).getValue());
        feuille.getRange(i - k, 6).setValue(Frais);
        if (feuille.getRange(i + j + 1, 16).getValue() == "Achat") {
          Valeur = (Valeur * Quantite + (parseInt(feuille.getRange(i + j + 1, 18).getValue()) * parseInt(feuille.getRange(i + j + 1, 15).getValue()))) / (Quantite + parseInt(feuille.getRange(i + j + 1, 15).getValue()));
          feuille.getRange(i - k, 7).setValue(Valeur);
        } else {
          feuille.getRange(i - k, 7).setValue(Valeur);
        }
        if (feuille.getRange(i + j + 1, 16).getValue() == "Vente") {
          Quantite = Quantite - parseInt(feuille.getRange(i + j + 1, 15).getValue());
          feuille.getRange(i - k, 5).setValue(Quantite);
        } else {
          Quantite = Quantite + parseInt(feuille.getRange(i + j + 1, 15).getValue());
          feuille.getRange(i - k, 5).setValue(Quantite);
        }
        TotalAchat = TotalAchat + parseInt(feuille.getRange(i + j + 1, 19).getValue());
        feuille.getRange(i - k, 8).setValue(TotalAchat);
        TotalVersements = TotalVersements + parseInt(feuille.getRange(i + j + 1, 20).getValue());
        feuille.getRange(i - k, 9).setValue(TotalVersements);
        j = j + 1;
      }
    } else {
      feuille.getRange(i - k - 1, 1).setValue(feuille.getRange(i, 11).getValue());
      feuille.getRange(i - k - 1, 2).setValue(feuille.getRange(i, 12).getValue());
      feuille.getRange(i - k - 1, 3).setValue(feuille.getRange(i, 13).getValue());
      feuille.getRange(i - k - 1, 4).setValue(feuille.getRange(i, 14).getValue());
      feuille.getRange(i - k - 1, 5).setValue(feuille.getRange(i, 15).getValue());
      feuille.getRange(i - k - 1, 6).setValue(feuille.getRange(i, 17).getValue());
      feuille.getRange(i - k - 1, 7).setValue(feuille.getRange(i, 18).getValue());
      feuille.getRange(i - k - 1, 8).setValue(feuille.getRange(i, 19).getValue());
      feuille.getRange(i - k - 1, 9).setValue(feuille.getRange(i, 20).getValue());
      j = 0;
    }
  }
};
Solution

  • Your script is recognising some values as strings.

    The reason is that when any blank cell in Column S or T is used in a formula, the result will be a string.

    Consider this answer based on your original code:

    Major changes

    /** @OnlyCurrentDoc */

function Synthese() {
  const feuille = SpreadsheetApp.getActive().getSheetByName('Synthèse Opérations');
  const Quantite=0;
  const Frais=0;
  const Valeur=0;
  var TotalAchat=0;
  var TotalVersements=0;

  feuille.activate;
  feuille.getRange('A2:I10000').clearContent();
  let j=0;
  let k=0;
  let row = 1

  // get the last row of data in in Column K 
  var kVals = feuille.getRange("K2:K").getValues();
  var kLast = kVals.filter(String).length;
  var lastRow = kLast+1
  //Logger.log("DEBUG: the number of rows of data in column K = "+kLast+" and the last row = "+lastRow)

  // loop through data in range K:T
  for (let i =2;i<=lastRow;i++) {
    i=i+j;
    row =row+1
    // Logger.log("DEBUG: New row "+i+" target row = "+row)
    // test for no data
    //Logger.log("DEBUG: test for no data: i: "+i+", test range = "+feuille.getRange(i,11).getA1Notation()+", value = "+feuille.getRange(i,11).getValue())
    if (feuille.getRange(i,11).getValue()=="") {
      //Logger.log("DEBUG: i:"+i+" value is blank")
      break
    }

    k=j+k;
    j=0;

    // test for match on next row
    // Logger.log("DEBUG: Test for match on next row: "+feuille.getRange(i,11).getA1Notation()+" (value = "+feuille.getRange(i,11).getValue()+") with "+ feuille.getRange(i+j+1,11).getA1Notation()+" (value = "+feuille.getRange(i+j+1,11).getValue()+")")
    if (feuille.getRange(i,11).getValue() == feuille.getRange(i+j+1,11).getValue()) {
      // Logger.log("DEBUG: match on next row is true")
      let Quantite=feuille.getRange(i,15).getValue();
      // Logger.log("DEBUG: Quantity Range = "+feuille.getRange(i,15).getA1Notation()+", value ="+feuille.getRange(i,15).getValue())
      let Frais=feuille.getRange(i,17).getValue();
      // Logger.log("DEBUG: Frais = "+feuille.getRange(i,17).getA1Notation()+", value ="+feuille.getRange(i,17).getValue())
      let Valeur=feuille.getRange(i,18).getValue();
      // Logger.log("DEBUG: Value Range = "+feuille.getRange(i,18).getA1Notation()+", value ="+feuille.getRange(i,18).getValue())
      TotalAchat=feuille.getRange(i,19).getValue();
      // Logger.log("DEBUG: Total Purchase cost Range = "+feuille.getRange(i,19).getA1Notation()+", value ="+feuille.getRange(i,19).getValue())
      

      // test for blank in Column T
      // Logger.log("DEBUG: TotalVersements = range: "+feuille.getRange(i,20).getA1Notation()+", (value = "+feuille.getRange(i,20).getValue()+")")
      if (typeof(feuille.getRange(i,20).getValue()) == "string"){
        // column T is blank, so assign default value
        // Logger.log("DEBUG: value in Column T is blank so assign default value")
        TotalVersements = 0
      }
      else{
        // column T is NOT blank so include in formula
        // Logger.log("DEBUG: Value in Column T is NOT blank so include in formula")
        TotalVersements=feuille.getRange(i,20).getValue()
      }
      // Logger.log("DEBUG: TotalVersements = "+TotalVersements)


      // while match the company on the next row
      // Logger.log("DEBUG: while match Company on next row: "+feuille.getRange(i+j,11).getA1Notation()+" (value = "+feuille.getRange(i+j,11).getValue()+") equals  "+ feuille.getRange(i+1+j,11).getA1Notation()+" (value = "+feuille.getRange(i+1+j,11).getValue()+")")
      while (feuille.getRange(i+j,11).getValue() == feuille.getRange(i+1+j,11).getValue()) {
       // Logger.log("DEBUG: Second Next row test is true")
    
        feuille.getRange(row,1).setValue(feuille.getRange(i+j+1,11).getValue());
        // Logger.log("DEBUG: Enterprice: "+feuille.getRange(row,1).getA1Notation()+" = "+feuille.getRange(i+j+1,11).getA1Notation()+", value = "+feuille.getRange(i+j+1,11).getValue())
        feuille.getRange(row,2).setValue(feuille.getRange(i+j+1,12).getValue());
        // Logger.log("DEBUG: Code: "+feuille.getRange(row,2).getA1Notation()+" = "+feuille.getRange(i+j+1,12).getA1Notation()+", value = "+feuille.getRange(i+j+1,12).getValue())
        feuille.getRange(row,3).setValue(feuille.getRange(i+j+1,13).getValue());
        // Logger.log("DEBUG: Sector; "+feuille.getRange(row,3).getA1Notation()+" = "+feuille.getRange(i+j+1,13).getA1Notation()+", value = "+feuille.getRange(i+j+1,13).getValue())
        feuille.getRange(row,4).setValue(feuille.getRange(i+j+1,14).getValue());
        // Logger.log("DEBUG: Date: "+feuille.getRange(row,4).getA1Notation()+" = "+feuille.getRange(i+j+1,14).getA1Notation()+", value = "+feuille.getRange(i+j+1,14).getValue())
   
        // Logger.log("DEBUG: Frais before = "+Frais)   
        // Logger.log("DEBUG: Frais next row = "+feuille.getRange(i+j+1,17).getA1Notation()+", value = "+feuille.getRange(i+j+1,17).getValue())   
        Frais=Frais+feuille.getRange(i+j+1,17).getValue();
        // Logger.log("DEBUG: Total Frais:  = "+feuille.getRange(i+j+1,17).getA1Notation()+", value = "+Frais)
        feuille.getRange(row,6).setValue(Frais);


        // test for ACHAT
        // Logger.log("DEBUG: test for ACHAT - if "+feuille.getRange(i+j+1,16).getA1Notation()+" (value = "+feuille.getRange(i+j+1,16).getValue()+") = Achat")
        if (feuille.getRange(i+j+1,16).getValue()=="Achat") {
          // Logger.log("DEBUG: Price per share formula: first row; ('valeur' ("+Valeur+") by 'Quantite' ("+Quantite+") plus second row 'valeur' "+feuille.getRange(i+j+1,18).getA1Notation()+" (value = "+feuille.getRange(i+j+1,18).getValue()+") by 'Quantite' "+feuille.getRange(i+j+1,15).getA1Notation()+" (value = "+feuille.getRange(i+j+1,15).getValue()+")) divided by ('first Quantite'="+Quantite+" plus 'second  Quantite' "+feuille.getRange(i+j+1,15).getA1Notation()+" (value = "+feuille.getRange(i+j+1,15).getValue()+"))")  
          Valeur=(Valeur*Quantite+feuille.getRange(i+j+1,18).getValue()*feuille.getRange(i+j+1,15).getValue())/(Quantite+feuille.getRange(i+j+1,15).getValue());
          feuille.getRange(row,7).setValue(Valeur);
          // Logger.log("DEBUG: Price per share result: "+feuille.getRange(row,7).getA1Notation()+" (value = "+Valeur+")")
        } else {
          // Logger.log("DEBUG: SALE Value: "+feuille.getRange(row,7).getA1Notation()+" = "+Valeur)  
          feuille.getRange(row,7).setValue(Valeur);
        }
        // Logger.log("DEBUG: test for ACHAT: END")

        // test for VENTE
        // Logger.log("DEBUG: test for VENTE - if "+feuille.getRange(i+j+1,16).getA1Notation()+" (value = "+feuille.getRange(i+j+1,16).getValue()+") = Vente")
        if (feuille.getRange(i+j+1,16).getValue()=="Vente") {
          // Logger.log("DEBUG: SALE: qty: "+Quantite+" less "+feuille.getRange(i+j+1,15).getA1Notation()+" (value ="+feuille.getRange(i+j+1,15).getValue()+")")  
          Quantite=Quantite-feuille.getRange(i+j+1,15).getValue();
          feuille.getRange(row,5).setValue(Quantite);
        } else {
          // Logger.log("DEBUG: quantity purchased formula: qty: "+Quantite+" plus "+feuille.getRange(i+j+1,15).getA1Notation()+" (value ="+feuille.getRange(i+j+1,15).getValue()+")")        
          Quantite=Quantite+feuille.getRange(i+j+1,15).getValue();
          // Logger.log("DEBUG: `Quantite` "+feuille.getRange(row,5).getA1Notation()+" = "+Quantite)
          feuille.getRange(row,5).setValue(Quantite);
        }
        // Logger.log("DEBUG: test for VENTE: END")

        // test for Column S = blank
        // Logger.log("DEBUG: test for Column S = blank")
        // Logger.log("DEBUG: TotalAChat = "+TotalAchat+" plus "+feuille.getRange(i+j+1,19).getA1Notation()+" (value= "+feuille.getRange(i+j+1,19).getValue()+")")
        // console.log("DEBUG: typeof-BEFORE: TotalAchat type = "+typeof(TotalAchat));
        if (typeof(feuille.getRange(i+j+1,19)) == "string"){
          // column S is blank, so exclude column S from formula
          // Logger.log("DEBUG: value in Column S is blank so exclude Column S from formula")
          TotalAchat = TotalAchat            
        }
        else{
          // column S is NOT blank, so include column S in formula
          // Logger.log("DEBUG: value in Column S is NOT blank so include Column S in formula")
          TotalAchat = TotalAchat + feuille.getRange(i+j+1,19).getValue();
        }
        // console.log("DEBUG: typeof-AFTER: TotalAchat type = "+typeof(TotalAchat));

        // Logger.log("DEBUG: TotalAChat: "+feuille.getRange(row,8).getA1Notation()+ " = "+TotalAchat)
        feuille.getRange(row,8).setValue(TotalAchat);


        // check if total sales value (column T) has a numeric value
        // if no, then assign default value
        // Logger.log("DEBUG: test for a blank in Column T")
        // Logger.log("DEBUG: Total sales = Totalversements = "+TotalVersements+" plus "+feuille.getRange(i+j+1,20).getA1Notation()+" (value = "+feuille.getRange(i+j+1,20).getValue()+")")
        // Logger.log("DEBUG: cell "+feuille.getRange(i+j+1,20).getA1Notation()+" type of "+typeof(feuille.getRange(i+j+1,20).getValue()))
        if (typeof(feuille.getRange(i+j+1,20).getValue()) =="string"){
          // Column T is blank, so exclude from formula
          // Logger.log("DEBUG: BLANK: Column T is blank, so exclude from formula")
        }
        else{
          // Column T is NOT blank, so exclude from formula
          // Logger.log("DEBUG: NOT BLANK: Column T is not blank, so include in  formula")
          TotalVersements = TotalVersements+feuille.getRange(i+j+1,20).getValue();
        }
        // Logger.log("DEBUG: typeof-AFTER: TotalVersements type = "+typeof(TotalVersements)+" value = "+TotalVersements)


        // test for TotalVersements = blank
        // Logger.log("DEBUG: test for TotalVersements is blank")
        // Logger.log("DEBUG: Total Versements = "+TotalVersements+" typeof = "+typeof(TotalVersements))
        if (typeof(TotalVersements) == "string"){
          // Logger.log("DEBUG: totalversements is blank so assign default value")
          TotalVersements = 0
        }
        feuille.getRange(row,9).setValue(TotalVersements)

        // if qty is zero, then reset variables
        // Logger.log("DEBUG: qty = "+Quantite+", if zero then reset variables")
        if (Quantite==0) {
          TotalAchat = 0;
          Frais=0;
          TotalVersements=0;
          // Logger.log("DEBUG: qty = 0, so TotalAchat, TotalVersements and Frais set to zero")
        }
        j=j+1;
      } // end of while
    } else 
    {
      // no match on next row
      // Logger.log("DEBUG: Next row test is false")
      // test for blank values
      feuille.getRange(row,1).setValue(feuille.getRange(i,11).getValue());
      feuille.getRange(row,2).setValue(feuille.getRange(i,12).getValue());
      feuille.getRange(row,3).setValue(feuille.getRange(i,13).getValue());
      feuille.getRange(row,4).setValue(feuille.getRange(i,14).getValue());
      feuille.getRange(row,5).setValue(feuille.getRange(i,15).getValue());
      feuille.getRange(row,6).setValue(feuille.getRange(i,17).getValue());
      feuille.getRange(row,7).setValue(feuille.getRange(i,18).getValue());
      // test for blank in Column S
      if (typeof(feuille.getRange(i,19).getValue()) == "string"){
        feuille.getRange(row,8).setValue(0);
      }
      else{
        feuille.getRange(row,8).setValue(feuille.getRange(i,19).getValue());
      }
      // test for blank in Column T
      if (typeof(feuille.getRange(i,20).getValue()) == "string"){
        feuille.getRange(row,9).setValue(0);
      }
      else{
        feuille.getRange(row,9).setValue(feuille.getRange(i,20).getValue());
      }
      j=0;
    }
} // end of for loop
Logger.log("DEBUG: End of processing")
}

    SAMPLE INPUT

    input

    SAMPLE OUTPUT

    output