CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
mahi
mahi

Reputation: 1

How can I copy specific values from 1 Google Sheet and paste to another Sheet using Google Script?

So I already have a script that is copy/pasting the entire row from one sheet to another. But I am interested in only copy/pasting specific cellls. For example, if column C = Approved, copy value in Column G in source sheet and paste to new row in column H of destination sheet. Below are example screenshots and my current code:

Source sheet (only copy email in columnG) Source Sheet

Destination sheet (paste email in columnH) Destination sheet

function copyrange() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Test 2'); //source sheet
  var testrange = sheet.getRange('C:C'); //range to check
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('Test'); //destination sheet
  var data = [];
  var j =[];

  //Condition check in H:H; If true copy the same row to data array
for (i=0; i<testvalue.length;i++) {
  if ( testvalue[i] == 'Approved') {
  data.push.apply(data,sheet.getRange(i+1,1,1,25).getValues());
  //Copy matched ROW numbers to j
  j.push(i);
 }
 }
//Copy data array to destination sheet

 csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

//Delete matched rows in the source sheet
  for (i=0;i<j.length;i++){
  var k = j[i]+1;
  sheet.deleteRow(k);

//Alter j to account for deleted rows
  if (!(i == j.length-1)) {
  j[i+1] = j[i+1]-i-1;
}
}
}

Is anyone able to help me with this!? I would greatly appreciate it :) Thank you!

Upvotes: 0

Views: 106

Answers (1)

arul selvan
arul selvan

Reputation: 624

I made some corrections. Could not test it without your sample sheet


function copyrange() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcsht = ss.getSheetByName('Test 2'); //source sheet
  var srcrange = sheet.getRange('C:G'); //range to check - edited
  var srcvals= testrange.getValues(); // edited
  var dessht = ss.getSheetByName('Test'); //destination sheet
  var emails =  [];
  var delrows =[];

  //Condition check in H:H; If true copy the same row to data array
for (i=0; i < srcvals.length;i++) {
  if ( srcvals[i][0] == 'Approved') {
   emails.push(srcvals[i][4]) ; //edited - col G of same row
  //Copy matched ROW numbers to j
  delrows.push(i);
 }
 }
//Copy data array to destination sheet

 dessht.getRange(dessht.getLastRow()+1,1,emails.length,emails[0].length).setValues(emails);

//Delete matched rows in the source sheet
  for (i= delrows.length; i > 0; i--){
   srcsht.deleteRow(delrows[i]+1);
}
}

Upvotes: 0

Related Questions