Google script for google sheets - Range copy paste special 'add'

Question

i'm really newbie at scripting, i used to do my work with microsoft office that have a special paste "add" feature and now for google sheet i can't really find it.

I will have a source range of C2:C102 and destination at same sheet D2:D102 i want the script (that i can run manually weekly) to copy all the range from source and sum it with the already existing data at D2:D102 (only values).

Here is a small example - Before after

I tried to use this code but ofc it just replaces the values.

function copyCells(){
   var thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var SourceSheet = thisSpreadsheet.getSheetByName("test");
  var SourceRange = thisSpreadsheet.getRange("C2:C102");

  var destinationSheet = thisSpreadsheet.getSheetByName("test");
  var destinationRange = destinationSheet.getRange("D2:D102");

   SourceRange.copyTo(destinationRange, {contentsOnly: true}); 
}

Any help will be really appreciated :)

JSmith JSmith · Accepted Answer · 2018-11-25T12:14:19

Haven't tested the code but try this.

grab values with getValues()
sum the values

copy back values with setValues()

function copyCells(){
   const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   const SourceSheet = spreadsheet.getSheetByName("test");
   const SourceRange = spreadsheet.getRange("C2:C102");
   const SourceValues = SourceRange.getValues();

   const destinationSheet = spreadsheet.getSheetByName("test");
   const destinationRange = destinationSheet.getRange("D2:D102");
   const destinationValues = destinationRange.getValues();

   for (let i = 0; i < SourceValues.length; i++)
     destinationValues[i][0] = parseFloat(destinationValues[i][0]) + parseFloat(SourceValues[i][0])

   destinationRange.setValues(destinationValues);
}

REFERENCES

range.getValues()

range.setValues()

Google script for google sheets - Range copy paste special 'add'

1 Answers