I want to CopyPaste the filtered rows data from google sheets based on the following conditions;
I will choose the value from the dropdown list in A2 of “sheet2”>The dropdown cell value matches the value within the header row of “Master sheet”> Filter the non-blank cell values based on matched column value> get the data of Column A2:A, G2:P of filtered rows And display the gathered data in “Sheet2” from A5 cell.
We can directly loop through the data in the “Master Sheet” and apply filtering conditions based on your requirements. Here’s the
function updateFilteredData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = ss.getSheetByName("Master Sheet");
var sheet2 = ss.getSheetByName("Sheet2");
var selectedValue = sheet2.getRange("A2").getValue();
var headerRow = masterSheet.getRange(1, 1, 1, masterSheet.getLastColumn()).getValues()[0];
var columnIndex = headerRow.indexOf(selectedValue) + 1;
if (columnIndex > 0) {
var dataRange = masterSheet.getRange(2, 1, masterSheet.getLastRow() - 1, masterSheet.getLastColumn());
var data = dataRange.getValues();
// Clear existing data in "Sheet2" starting from A5
sheet2.getRange("A5:ZZ").clearContent();
var filteredData = [];
for (var i = 0; i < data.length; i++) {
if (data[i][columnIndex - 1] !== "" && data[i][columnIndex - 1] !== undefined) {
var rowData = [data[i][0]]; // Column A
for (var j = 53; j <= 58; j++) { // Columns BA to BG
rowData.push(data[i][j - 1]);
}
rowData.push(data[i][columnIndex - 1]); // Matched column
filteredData.push(rowData);
}
}
// Paste the filtered data to "Sheet2" starting from A5
sheet2.getRange(5, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
}
}
script:
In this script, we manually loop through the rows in the “Master Sheet,” filter them based on your conditions, and then paste the filtered data into “Sheet2.”
Make sure to replace "Master Sheet" and "Sheet2" with your actual sheet names. Additionally, please adapt the index range like; BA to BG columns (53 to 58) based on the actual column indices you’re interested in.
Please try this updated script, and it should work without requiring advanced services.