javascript - have a code for copying rows from a sheet to another in google sheet but it do all of the rows not working for sele

admin管理员组

文章数量:1122846

I have a code for copying rows from a sheet (Parks) to another (Report) in google sheet. It works fine but the problem is, I have 335 rows in sheet 'Parks' and I don't want all of these rows in my Sheet 'Report'.

I want to apply filter on sheet 'Parks' so it might remain like 14 rows in sheet 'Parks'. I want the code to copy just these 14 rows not all of them (not all the 335 of them) but now the code is copying all of them.

Anyone can help to modify the code?

Here is the code:

function copyDataFromParksToReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var parksSheet = ss.getSheetByName('Parks');
  var reportSheet = ss.getSheetByName('Report');
  
  reportSheet.getRange('A2:M').clearContent();
  
  var parksData = parksSheet.getRange('A4:Q').getValues();
  
  var reportData = [];
  
  parksData.forEach(function(row) {
    var newRow = [];
    newRow[0] = row[16]; // Column Q -> Column A
    newRow[1] = row[0]; // Column A -> Column B
    newRow[2] = row[4]; // Column E -> Column C
    newRow[3] = row[3]; // Column D -> Column D
    newRow[4] = row[1]; // Column B -> Column E
    newRow[5] = row[5]; // Column F -> Column F
    newRow[6] = row[8]; // Column I -> Column G
    newRow[8] = row[10]; // Column K -> Column I
    newRow[10] = row[9]; // Column J -> Column K
    newRow[12] = row[12]; // Column M -> Column M
    
    // Get File ID from Google Drive links
    if (row[6].includes("drive.google")) {
      newRow[7] = row[6].split("/")[5]; // Column G -> Column H
    } else {
      newRow[7] = '';
    }
    if (row[11].includes("drive.google")) {
      newRow[11] = row[11].split("/")[5]; // Column L -> Column L
    } else {
      newRow[11] = '';
    }
    
    reportData.push(newRow);
  });
  
  reportSheet.getRange(2, 1, reportData.length, reportData[0].length).setValues(reportData);
}

I have tried the getfilter class but it didn't work.

After applying filter:

Before applying filter:

After running code + after applying filter:

I have a code for copying rows from a sheet (Parks) to another (Report) in google sheet. It works fine but the problem is, I have 335 rows in sheet 'Parks' and I don't want all of these rows in my Sheet 'Report'.

I want to apply filter on sheet 'Parks' so it might remain like 14 rows in sheet 'Parks'. I want the code to copy just these 14 rows not all of them (not all the 335 of them) but now the code is copying all of them.

Anyone can help to modify the code?

Here is the code:

function copyDataFromParksToReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var parksSheet = ss.getSheetByName('Parks');
  var reportSheet = ss.getSheetByName('Report');
  
  reportSheet.getRange('A2:M').clearContent();
  
  var parksData = parksSheet.getRange('A4:Q').getValues();
  
  var reportData = [];
  
  parksData.forEach(function(row) {
    var newRow = [];
    newRow[0] = row[16]; // Column Q -> Column A
    newRow[1] = row[0]; // Column A -> Column B
    newRow[2] = row[4]; // Column E -> Column C
    newRow[3] = row[3]; // Column D -> Column D
    newRow[4] = row[1]; // Column B -> Column E
    newRow[5] = row[5]; // Column F -> Column F
    newRow[6] = row[8]; // Column I -> Column G
    newRow[8] = row[10]; // Column K -> Column I
    newRow[10] = row[9]; // Column J -> Column K
    newRow[12] = row[12]; // Column M -> Column M
    
    // Get File ID from Google Drive links
    if (row[6].includes("drive.google.com")) {
      newRow[7] = row[6].split("/")[5]; // Column G -> Column H
    } else {
      newRow[7] = '';
    }
    if (row[11].includes("drive.google.com")) {
      newRow[11] = row[11].split("/")[5]; // Column L -> Column L
    } else {
      newRow[11] = '';
    }
    
    reportData.push(newRow);
  });
  
  reportSheet.getRange(2, 1, reportData.length, reportData[0].length).setValues(reportData);
}

I have tried the getfilter class but it didn't work.

After applying filter:

Before applying filter:

After running code + after applying filter:

• javascript
• google-sheets
• filter
• copy
• row

Share Improve this question Follow edited Nov 22, 2024 at 23:04 Laurent FAVOLE 38344 silver badges1515 bronze badges asked Nov 22, 2024 at 10:14 KimiagarKimiagar 122 bronze badges 3

• 1 Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this anonymous sheet maker to create your reproducible example without your email being exposed. – Patsytalk Commented Nov 22, 2024 at 10:43
• i wanna apply filter on sheet 'Parks' How/where is the filter selected; what column of the data does the filter refer to? Is this question about how to select the filter, how to apply the filter, how to trigger the script or all the above? – Tedinoz Commented Nov 22, 2024 at 11:37
• for many different requests, i need to apply different kind of filters so i can't mention any. i edited my question and added 3 images to show what i mean. Jats PPG almost got what i need – Kimiagar Commented Nov 22, 2024 at 12:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Use isRowHiddenByFilter() to Fetch

Since getVisibleValues() is not working to fetch the filtered data, I have used an alternate solution, which is the isRowHiddenByFilter() to get the not hidden by the filter.

I have changed the whole code to get your desired output.

Modified Script

 function getVisibleValuesInColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Parks");
  var vl = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  var op = vl.filter((_, i) => !sh.isRowHiddenByFilter(i + 1));
  console.log(op);
  var rp = ss.getSheetByName("Report");
  rp.clear();
  ss.getSheetByName("Report").getRange(1, 1, op.length, op[0].length).setValues(op);
}

Sample Output

References:

• isRowHiddenByFilter(rowPosition)

• Logical NOT (!)

本文标签：