Home > Back-end >  How to subtract one row from another and place the values into a third row in Google Sheets
How to subtract one row from another and place the values into a third row in Google Sheets

Time:12-23

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:

  • Related