CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Tom
Tom

Reputation: 7

How to copy a range from one sheet to a range in another sheet, then repeat copying to the next row down

I am looking for a google apps script to copy a range of cells in one sheet to another sheet, then when the first range values are user changed, copy the same range (new values), to the next row down from the first one. Here is what i have so far:

function copyToDatasheet() {

     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var s1 = ss.getSheetByName('Mobile Form');
     var s2 = ss.getSheetByName('Data'); 

     s1.getRange('B2').copyTo(s2.getRange('P6'), {contentsOnly:true})
     s1.getRange('B4').copyTo(s2.getRange('Q6'), {contentsOnly:true})
     s1.getRange('B6').copyTo(s2.getRange('R6'), {contentsOnly:true})
     s1.getRange('B8').copyTo(s2.getRange('S6'), {contentsOnly:true})
     s1.getRange('B10').copyTo(s2.getRange('O6'), {contentsOnly:true})
}

All this does is copy s1 ranges to s2 ranges starting on row 6, which is how I want it to start but on the next entry I want it to move down to row 7 and so on. s1 ranges are reset and new values entered, copy them to s2 in next row down Having them dynamically move down on next run. Hope this makes sense, I do not have a ton of knowledge about apps script so explaining what some functions do would be helpful if possible. Thanks for any assistance anyone can offer.

Upvotes: 1

Views: 361

Answers (1)

Jason Allshorn
Jason Allshorn

Reputation: 1625

This will get the same data range when there is an edit to the Sheet1. Then all of that data will then be appended to the Sheet2. 

function getMultipleValues(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  var range = sheet.getRange(2,1,4,2); //getRange(starting row, starting column, ending row, ending column)
  var data = range.getValues();
  putMultipleValues(data)
}

function putMultipleValues(data){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet2');
  data.forEach(function(i){
    SpreadsheetApp.flush();
    sheet.appendRow(i)
  })
}

Don't forget to set the triggers. In the menu => Edit => Current project's triggers. enter image description here

Upvotes: 1

Related Questions