0
288
VisualizaçõesHow can i add multiple values to multiple cells?
I've made an example in the pictures below of how it should look like.
I would like to add stock to the current stock by using the script.
If we take the images as an example then it would mean that the add stock column needs to add to the current stock column, instead of overwriting it.
Also this needs to happen in a different sheet.
This means that in the example add stock value the cell G5 should be 10 in the current stock sheet.
If any more questions feel free to ask.
Question:
The scrippt that i'm using right now is the following:
function V6() {
const ss = SpreadsheetApp.getActive();
const sh0 = ss.getSheetByName('Sheet0');
const sh1 = ss.getSheetByName('Sheet1');
const vs = sh0.getRange('G4:I586').getValues();
vs.forEach(r => { r[2] += r[0]; r[0] = 0; });
[{in:0,out:7,sht:sh0},{in:2,out:9,sht:sh1}].forEach(e => {
sh0.getRange(4, e.out, vs.length, 1).setValues(vs.map(r => [r[e.in]]));
});
}
If I run this scrippt all the values from G4:G586 in Sheet0 will add up to I4:I586 in Sheet0
But what I want the scrippt to do is add the value from G4:G586 in Sheet0 to I4:I586 in Sheet 1.
sh0.getRange(4, e.out, vs.length, 1).setValues(vs.map(r => [r[e.in]]));
If i change sh0 to sh1 in the row above, then the values won't add up but overwrite it.
Could you help me with the solution to make it add up on a different sheet?
1 Respostas
Responde à pergunta0
Calculate and Load the columns that have changed
function inboeken() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet()
const vs = sh.getRange('G4:I586').getValues();//getting all of the data at one time saves time
vs.forEach(r => { r[2] += r[0]; r[0] = 0; });
[{in:0,out:7},{in:2,out:9}].forEach(e => {
sh.getRange(4, e.out, vs.length, 1).setValues(vs.map(r => [r[e.in]]));//It only loads the two columns that have changed thus eliminating the risk of damaging formulas
});
}
This array maps the indices in the data to columns in the spreadsheet
[{in:0,out:7},{in:2,out:9}]
Additional Question:
Adding a sheet
function inboeken() {
const ss = SpreadsheetApp.getActive();
const sr = 4;
const sh0 = ss.getSheetByName('Sheet0');
const sh1 = ss.getSheetByName('Sheet1');
const vs = sh0.getRange('G4:I586').getValues();//getting all of the data at one time saves time
vs.forEach(r => { r[2] += r[0]; r[0] = 0; });
[{in:0,out:7,sht:sh0},{in:2,out:9,sht:sh0},{in:2,out:9,sht:sh1},].forEach(e => {
let sh = e.sht;
sh.getRange(sr, e.out, vs.length, 1).setValues(vs.map(r => [r[e.in]]));//It only loads the two columns that have changed thus eliminating the risk of damaging formulas
});
}