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

Question

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!

It will be better if you share the sheet with public edit rights and give the link. What you want to achieve is not clear. If you want to transfer "Approved" lines from source to destination, I suggest you introduce a new column with tile "updated" and use that column to update the destination sheet and set value to "Updated", instead of deleting it. You can run this function periodically automatically — arul selvan
what is the problem you are facing with your existing code? I can see that deleting by row number will work only if you go from bottom to top. Otherwise, the row numbers will change with deleting and your loop will fail — arul selvan
@arulselvan sorry for the confusion. So right now, it's looking to see which rows in the source sheet say "approved" in column C. If approved == True, then I want to copy value in column G in source sheet and paste to column H of destination sheet. I think I need to update get GetRange element but can't figure out what it needs to be. Right now, it just copy/pastes all values (the entire row). Does this clarify? — mahi
There is some addon specifically created for such purposes. Please check if you are interested in addons. gsuite.google.com/u/0/marketplace/app/flow/909859924833 — Anees Hameed
@AneesHameed thanks! definitely will try this on my personal laptop. Unfortunately, I can use this add-on at my organization, which is where I am running into the above script issue. — mahi

arul selvan arul selvan · Accepted Answer · 2020-05-08T16:52:20

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);
}
}

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

1 Answers