Copy pasting dynamic data through Google Apps Script or Formulas

user_19288993 0
Viewed 38

Good morning,

I am using the following script right now to get the following situation: For a lead list which is dynamic so constantly moves, we want to extract certain information if they fall in a specific category. "C-Grade"

Essentially all data from all "C Grade" rated people, need to be copied from "Data info" onto "Lead info"

Normally I would use =vlookup but since its dynamic and constantly changing, I don't see the option to use a formula.

App Script I am using right now:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Data info");
  var pasteSheet = ss.getSheetByName("Lead info");

  // get source range
  var source = copySheet.getRange(1,12);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
  source.clearContent();
}

Here is the example file: Example file

google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • You want to copy the rows which have the value of C Grade in the column "D" of "Data info" sheet to "Lead info" sheet.

In this case, how about the following modification?

Modified script:

function copyInfo() {
  var check = "C Grade"; // This is from your question.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Data info");
  var pasteSheet = ss.getSheetByName("Lead info");
  var values = copySheet.getRange("A2:D" + copySheet.getLastRow()).getValues().filter(r => r[3] == check);
  pasteSheet.getRange(pasteSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

  • When I saw your script, the value of "L1" of "Data info" is copied to "B1:C12" of "Lead info". When I saw your provided Spreadsheet, "L1" of "Data info" has no value.

  • In this modification, the values of "Data info" are retrieved and filtered by "C Grade" of the column "D", and the filtered values are put to "Lead info" sheet.

Reference:

Added 1:

When I saw your provided sample Spreadsheet again, I noticed that your script has been changed. And, I noticed that 2 points are different from your initial sample Spreadsheet.

  1. In your initial Spreadsheet, "ABC Grade" was column "D". But, in your current Spreadsheet, that is colum "E".
  2. In my proposed script, I proposed var values = copySheet.getRange("A2:D" + copySheet.getLastRow()).getValues().filter(r => r[3] == check);. But in your current script, that is changed to var values = copySheet.getRange("A1:D10" + copySheet.getLastRow()).getValues().filter(r => r[0] == check);/

I think that the reason for your current issue is that you changed your initial Spreadsheet and my proposed script. When your current Spreadsheet is used, the sample script is as follows.

Sample script:

function copyInfo() {
  var check = "C Grade"; // This is from your question.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Data info");
  var pasteSheet = ss.getSheetByName("Lead info");
  var values = copySheet.getRange("A1:E" + copySheet.getLastRow()).getValues().filter(r => r[4] == check);
  if (values.length == 0) return;
  pasteSheet.getRange(pasteSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}
  • When values has no value, an error occurs. So, I added if (values.length == 0) return;.

Added 2:

From your following new 2nd question,

Would there also be a way to only get certain colums. In this case A,B,D & E.

In this case, how about the following sample script?

Sample script:

function copyInfo() {
  var check = "C Grade"; // This is from your question.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Data info");
  var pasteSheet = ss.getSheetByName("Lead info");
  var values = copySheet.getRange("A1:E" + copySheet.getLastRow()).getValues().filter(r => r[4] == check).map(r => [1, 2, 4, 5].map(e => r[e - 1]));
  if (values.length == 0) return;
  pasteSheet.getRange(pasteSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}
user_71086530
Related