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
var TotalAchat=0andvar TotalVersements=0- changed these from "const" because of repeated errors such as:
ReferenceError: Cannot access 'TotalVersements' before initialization
- these errors were occurring because of instances where the variable was called before an earlier
let. The OP may wish to return these variables to "const" and edit the code to their satisfaction.
- changed these from "const" because of repeated errors such as:
let row = 1androw =row+1- the existing code would overwrite the previous row when a new investment was added to the mix. The solution was:
- increment a
rowvariable each time theforloop was executed. - replace all instances of range row =
i-kandi-k-1withrow
- increment a
- the existing code would overwrite the previous row when a new investment was added to the mix. The solution was:
for (let i =2;i<=lastRow;i++)- the original code ran for 10000 rows (admittedly with abreakas soon as a blank cell was found)- the code is modified to count the number of values in Column K and calculate the "lastRow".
getlastRowdoes NOT work because the values are generated by formula.
// test for blank in Column T- the original formula is
TotalVersements=feuille.getRange(i,20).getValue()however in the case of Achat, the value in Column T is blank. if (typeof(feuille.getRange(i,20).getValue()) == "string"){- added this test;
- if a string, then
TotalVersements = 0elseTotalVersements=feuille.getRange(i,20).getValue()
- the original formula is
// test for Column S = blank(similar to the test for Column T)// test for TotalVersements = blank(similar to others)
/** @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
SAMPLE OUTPUT
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices