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 :)

CodePudding user response：

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

CodePudding user response：

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