Hello everyone,
I'm turning to you because I can't get my code to work on google Sheets. The purpose of the program is to be able to follow the evolution of the stock of the various listed references. To do this, as soon as a change is made, the date and stock status should be stored in a further box (hence the references alone at the bottom of the sheet). For the moment the code does this: When you enter 1 or -2 for example in the "+/-" column, the stock and consumption are updated then the "+/-" column is reset to zero. The code is commented so that you can understand my approach. Thank you to anyone who can help me I'm really struggling to make the program.
function onEdit(){
if(SpreadsheetApp.getActiveRange().getColumn()==4){ ///things only happen if the "+/-" column is changed
for (var i in 2,403){
var cellTest = SpreadsheetApp.getActive().range.getCell(i,4); /// we retrieve each box of the "+/-" column
if( cellTest != 0){ //// we test if it change
var k =408+i; /// hint for date display lines
var l = 2; //// index for date display columns
var cellDate = SpreadsheetApp.getActive().range.getCell(k,l);
while (isEmpty(cellDate)==false){ ///the goal is to put the new date next to the previous one
l=l+1;
}
const cellDateDef = SpreadsheetApp.getActive();
cellDateDef.getRange(k,l).setValue(Date) /// on affiche la date du changement de stock
const cellStock = SpreadsheetApp.range.getCell(i,5);
SpreadsheetApp.range.getCell(k+1,5) = cellStock; ///in the box under the date of the change the stock status is displayed
}
}
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('G2:G403').setValue('0'); ////we reset the "+/-" column to zero
}
}Here there is the pictures to complete and understand my question: the google sheet end of the sheet
There are two formulas in the columns:
Stock: =D2+E2
Consommation: =IF(D2<0;-D2+G2;G2)
the sheet is made in iterative calculation mode
Thanks for your help
