Google Apps Script: CopyTo from one tab to another tab when the data met criteria

Question

I am new to apps script as well as this community. I am facing problem with apps script when I want to copy data from one tab to another tab.

I would like to copy the row (not whole row A:Z but only A:F) when column F is remarked as "Closed", copy the row from leads tab to customers tab.

This is the script that I did but it did not work at all

*function copyRowsWithCopyTo() {
  let spreadSheet = spreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Leads');

  let sourceRange = sourceSheet.getDataRange();
  let targetSheet = spreadsheet.getSheetByName('test');

  sourceRange.copyTo(targetSheet.getRange(3,2));
}*

Anyone can help? Thank you :)

Answer 1

Copy Row with formats

function copyRows() {
  let ss = SpreadsheetApp.getActive();
  let sh0 = ss.getSheetByName('Leads');
  let rg0 = sh0.getRange("A1:F" + sh0.getLastRow())
  let tsh = ss.getSheetByName('test');
  const values = rg0.getValues().forEach((r,i) => {
    if(r[5] == "Closed") {
      sh.getRange(i+1,1,1,6).copyTo(tsh.getRange(tsh.getLastRow() + 1,1));
    }
  });
}

Answer 2

I believe your goal is as follows.

• You want to copy the values from "Leads" sheet to "test" when the column "F" has the value of Closed. In this case, you want to copy the columns "A" to "F" to "B3" of "test" sheet.

Modification points:

• I think that spreadsheetApp of let spreadSheet = spreadsheetApp.getActiveSpreadsheet(); is SpreadsheetApp.
• spreadsheet of let targetSheet = spreadsheet.getSheetByName('test'); is not declaread. From your script, I thought that it's spreadSheet.
• When getDataRange() is used, all data range is retrieved. In this case, you can retrieve the range of A1:F.

When these points are reflected in your script, it becomes as follows.

Modified script:

function copyRowsWithCopyTo() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Leads');
  let sourceRange = sourceSheet.getRange("A1:F" + sourceSheet.getLastRow())
  let targetSheet = spreadSheet.getSheetByName('test');
  const values = sourceRange.getValues().filter(r => r[5] == "Closed");
  targetSheet.getRange(3, 2, values.length, values[0].length).setValues(values);
}

Note:

• In your question, you say copy the row from leads tab to customers tab. But in your script, test is used as the destination sheet. So, I used test as the destination sheet. Please be careful this.

References:

• getValues()
• setValues(values)
• filter()

Added:

From your following comment,

I want to copy the data in column A:K from "Leads" to "test" when column L in "Leads" is "Closed". I have entered the modified script that you written, but it is not working. Can understand?

I understood as follows.

• You want to copy the values from "Leads" sheet to "test" when the column "L" has the value of Closed. In this case, you want to copy the columns "A" to "K" to "B3" of "test" sheet.

In this case, how about the following sample script?

Sample script:

function copyRowsWithCopyTo2() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Leads');
  let sourceRange = sourceSheet.getRange("A1:L" + sourceSheet.getLastRow());
  let targetSheet = spreadSheet.getSheetByName('test');
  const values = sourceRange.getValues().flatMap(r => r.pop() == "Closed" ? [r] : []);
  targetSheet.getRange(3, 2, values.length, values[0].length).setValues(values);
}

Added

In your 1st question, you said as follows.

I would like to copy the row (not whole row A:Z but only A:F) when column F is remarked as "Closed", copy the row from leads tab to customers tab.

And, your showing script is as follows.

function copyRowsWithCopyTo() {
  let spreadSheet = spreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Leads');

  let sourceRange = sourceSheet.getDataRange();
  let targetSheet = spreadsheet.getSheetByName('test');

  sourceRange.copyTo(targetSheet.getRange(3,2));
}

When I proposed an answer for this your 1st question, you changed your question as follows.

I want to copy the data in column A:K from "Leads" to "test" when column L in "Leads" is "Closed". I have entered the modified script that you written, but it is not working. Can understand?

So, I proposed a sample script to your 2nd question. When I proposed an answer for your 2nd quesiton, you said it is not solved yet. And you provide the sample Spreadsheet as follows.

• Input: "Leads" sheet.

  

• Output: "IMM" sheet.

  

When I saw your provided sample script, your input sheet "Leads" has the data of columns "A" to "L".

But when I saw your destination sheet "IMM", it seems that it is required to put the data from the cell "A2". This is different from sourceRange.copyTo(targetSheet.getRange(3,2)); in your showing script. And also, it seems that you wanted to copy the columns "B", "C", "D", "I", "J", "K" from the columns "A:L" to the cell "A2" of the destination sheet. I think that this is the reason for your current issue of it is not solved yet.

As the result, I understood that you wanted to achieve the following situation.

When you want to retrieve the values from columns "A" to "L", and by checking "Closed" of the column "L", you want to put the values of columns "B", "C", "D", "I", "J", "K" to the cell "A2" of the destination sheet.

In order to achieve your final goal, please test the following sample script.

function copyRowsWithCopyTo3() {
  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sourceSheet = spreadSheet.getSheetByName('Leads');
  let sourceRange = sourceSheet.getRange("A1:L" + sourceSheet.getLastRow());
  let targetSheet = spreadSheet.getSheetByName('IMM');
  const values = sourceRange.getValues().flatMap(([, b, c, d, , , , , i, j, k, l]) => l == "Closed" ? [[b, c, d, i, j, k]] : []);
  console.log(values)
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

When this script is run for your sample Spreadsheet, the following result is obtained.