Fairly new to this - trying to write a script in Google Sheets to subtract values across two rows (row 4 minus row 3) and place those values into a third row (row 2) overwriting anything that is already there.
I am attempting to say B4 to D4 minus B3 to D3 must be placed into B2 to D2.
The script that I have tried is:
function getAchieved() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getSheetByName('TARGETS');
var endVal=s.getRange(4,2,1,3).getValue()
var startVal=s.getRange(3,2,1,3).getValue()
var achievedVal=endVal-startVal
s.getRange(2,2,1,3) .setValue(achievedVal)
}
However, this calculates only the first value and inserts that value into all the cells across row 2.
Help appreciated in advance!
CodePudding user response:
function getAchieved() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getSheetByName('TARGETS');
var endVal=s.getRange(4,2,1,3).getValues()[0];
var startVal=s.getRange(3,2,1,3).getValues()[0];
var achievedVal=endVal.map((v,i)=>v-startVal[i]);
s.getRange(2,2,1,3).setValues([achievedVal])
}
Reference: