copy spreadsheet with filter-CodePudding

Hello I am trying to copy one spreadsheet to another but I would like to use a filter so that only copies rows that contain certain values in column D (Jean, Fred, Mary). I would also like it to add a TimeStamp to the first column for the copied rows each time the script is run.

The script will run daily and it is meant to completely replace the previous days information, not append to it.

I cobbled a script together that works fine for copying the whole spreadsheet but how do I just copy certain rows based on the names in column D?

Thanks so much for your help!

var sourceSpreadsheetID= "Spreadsheet1 ID";
var sourceWorksheetName= "BusinessDetails";
var targetSpreadsheetID= "Spreadsheet2 ID";
var targetWorksheetName= "Sheet5";

function importBusinessDetailsData(){
  var thisSpreadsheet=SpreadsheetApp.openById("Spreadsheet1 ID");
  var thisWorksheet=thisSpreadsheet.getSheetByName("BusinessDetails");
  var thisData=thisWorksheet.getDataRange();

  var toSpreadsheet=SpreadsheetApp.openById("Spreadsheet2 ID");
  var toWorksheet=toSpreadsheet.getSheetByName("Sheet5");
  var toRange=toWorksheet.getRange(1,2, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues());

setTimeStamp()
}

function setTimeStamp() {
SpreadsheetApp.getActive().getSheetByName('Sheet5')
.getRange('A2:A').setValue(new Date())
}

CodePudding user response：

I believe your goal is as follows.

You want to copy the values from "BusinessDetails" sheet of "Spreadsheet1 ID" to "Sheet5" sheet of "Spreadsheet2 ID". At this time, you want to copy the specific rows by filtering the column "D". You want to copy the rows including the values Jean, Fred, Mary in the column "D". And, you want to add the timestamp to the 1st column.

In this case, how about the following modification?

From:

  var toRange=toWorksheet.getRange(1,2, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues());

setTimeStamp()

To:

var timeStamp = new Date();
var checkValues = ["Jean", "Fred", "Mary"];
var values = thisData.getValues().filter(r => checkValues.some(e => r[3].includes(e))).map(r => [timeStamp, ...r]);
if (values.length > 0) {
  toWorksheet.getRange(1, 2, values.length, values[0].length).setValues(values);
}

In this modification, from only copies rows that contain certain values in column D (Jean, Fred, Mary), the rows contains the values of Jean, Fred, Mary in the column "D" are retrieved. But, when you want to retrieve the rows that the values of Jean, Fred, Mary are existing in the column "D", please modify as follows.
- From
```
  var values = thisData.getValues().filter(r => checkValues.some(e => r[3].includes(e))).map(r => [timeStamp, ...r]);
```
- To
```
  var values = thisData.getValues().filter(r => checkValues.includes(r[3])).map(r => [timeStamp, ...r]);
```

From:

To:

References: