How can i add multiple values to multiple cells?-CodePudding

For my stocklist i want to easily add my stockvalue by using a macro.

right now i have the code below:

function inboeken() {
  var spreadsheet = SpreadsheetApp.getActive();
  ss=SpreadsheetApp.getActiveSpreadsheet()
  s=ss.getActiveSheet()
  var currVal=s.getRange('G4:G586').getValue()
  var currVal2=s.getRange('I4:I586').getValue()
  var plusVal=currVal   currVal2

  s.getRange('I4:I586') .setValue(plusVal)

  s.getRange("G4:G586").setValue(0)
}

I need the code to add the value from column G to column I in the same row, so for an example: If the value in G8 is 5, then by using the script the value from G8 needs to be added up in cell I8.

The code that i have right now adds the value from G4 to I4:I586.

Anyone know how i can make this work like i want to, without using to much rows.

CodePudding user response：

Caculate 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}]

forEach
map