I have a master and multiple copies of a sheets workbook. I would like to update the master and have the copies update AND filter the update according to a specified name/id value.
I have used the following script to make update copies, but it copies all information from the source sheet. I need to adjust this code so that the information will only copy over if the value in the third column matches a value in another cell of another sheet of the destination workbook.
I embedded a link to an image that has a very basic example of what my master workbook looks like. Each workbook has multiple sheets whose displays are dependent on data in the "independent data" tab. The master workbook contains data for my entire district and there is one copy of each workbook for each campus in the district. I want to be able to update the "independent data" tab of the master copy and have the campus copies be automatically updated with only the records where the Campus Name column matches their campus name.
I have successfully used the importrange function to do this when I only had few campuses, but I am now having to produce this workbook for many more, and I would like a way to quickly establish the importrange tether without manually entering the formula and sharing permissions one copy by one.
.png
function tetheredEdit(e) {
// The ID of the source spreadsheet
var sourceSpreadsheetId = 'sourceid'; // Replace with your source sheet ID
var sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName('student_data');
var sourceRange = sourceSheet.getRange('A3:S5000'); // Sync the entire sheet
var values = sourceRange.getValues();
// Folder ID where target sheets are stored
var folderId = 'folderId'; // Replace with your folder ID
var folder = DriveApp.getFolderById(folderId);
// Get all the files (spreadsheets) in the folder
var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
// Loop through each file in the folder and update all sheets
while (files.hasNext()) {
var file = files.next();
var targetSpreadsheet = SpreadsheetApp.open(file);
var sheets = targetSpreadsheet.getSheetByName('student_data');
// Loop through each sheet in the spreadsheet
sheets.getRange('A3:S5000').clearContent();
// Set the data into the target sheet
sheets.getRange(3, 1, values.length, values[0].length).setValues(values);
};
}
I have a master and multiple copies of a sheets workbook. I would like to update the master and have the copies update AND filter the update according to a specified name/id value.
I have used the following script to make update copies, but it copies all information from the source sheet. I need to adjust this code so that the information will only copy over if the value in the third column matches a value in another cell of another sheet of the destination workbook.
I embedded a link to an image that has a very basic example of what my master workbook looks like. Each workbook has multiple sheets whose displays are dependent on data in the "independent data" tab. The master workbook contains data for my entire district and there is one copy of each workbook for each campus in the district. I want to be able to update the "independent data" tab of the master copy and have the campus copies be automatically updated with only the records where the Campus Name column matches their campus name.
I have successfully used the importrange function to do this when I only had few campuses, but I am now having to produce this workbook for many more, and I would like a way to quickly establish the importrange tether without manually entering the formula and sharing permissions one copy by one.
https://i.sstatic.net/1kWiKE3L.png
function tetheredEdit(e) {
// The ID of the source spreadsheet
var sourceSpreadsheetId = 'sourceid'; // Replace with your source sheet ID
var sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName('student_data');
var sourceRange = sourceSheet.getRange('A3:S5000'); // Sync the entire sheet
var values = sourceRange.getValues();
// Folder ID where target sheets are stored
var folderId = 'folderId'; // Replace with your folder ID
var folder = DriveApp.getFolderById(folderId);
// Get all the files (spreadsheets) in the folder
var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
// Loop through each file in the folder and update all sheets
while (files.hasNext()) {
var file = files.next();
var targetSpreadsheet = SpreadsheetApp.open(file);
var sheets = targetSpreadsheet.getSheetByName('student_data');
// Loop through each sheet in the spreadsheet
sheets.getRange('A3:S5000').clearContent();
// Set the data into the target sheet
sheets.getRange(3, 1, values.length, values[0].length).setValues(values);
};
}
Since your current code works by fetching the entire dataset (A3:S5000) from the master spreadsheet and directly copying it into the target campus sheets without any filtering based on campus name or other conditions.
You mentioned:
I would like to update the master and have the copies update AND filter the update according to a specified name/id value.
I need to adjust this code so that the information will only copy over if the value in the third column matches a value in another cell of another sheet of the destination workbook.
The master workbook contains data for my entire district and there is one copy of each workbook for each campus in the district. I want to be able to update the "independent data" tab of the master copy and have the campus copies be automatically updated with only the records where the Campus Name column matches their campus name.
I just added this part to your current code, which includes a step to get the campus name from cell A1 in the independent data sheet of each target campus file and filter the data based on that name.
var campusName = campusSheet.getRange('A1').getValue();
var filteredValues = values.filter(row => row[2] === campusName);
You may try this Code:
function tetheredEdit() {
var sourceSpreadsheetId = 'change it with your sourceID';
var sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName('student_data');
var sourceRange = sourceSheet.getRange('A3:S5000');
var values = sourceRange.getValues();
var folderId = 'Change it with your folderID';
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
while (files.hasNext()) {
var file = files.next();
var targetSpreadsheet = SpreadsheetApp.open(file);
var targetSheet = targetSpreadsheet.getSheetByName('student_data');
var campusSheet = targetSpreadsheet.getSheetByName('independent data');
var campusName = campusSheet.getRange('A1').getValue();
if (!campusName) continue;
var filteredValues = values.filter(row => row[2] === campusName);
targetSheet.getRange('A3:S5000').clearContent();
if (filteredValues.length > 0) {
targetSheet.getRange(3, 1, filteredValues.length, filteredValues[0].length).setValues(filteredValues);
}
}
}
It filters the data based on the campus name found in the independent data sheet (cell A1) of each campus file. It only copies over rows where the campus name matches the value in cell A1 of the campus file.
Here is the Sample Master file:
After Running the Script:
You can add a trigger so that every time you change cell A1 in the Independent data, the data will be automatically filtered based on the new value.
Note: If this does not answer your question or if my answer is quite different from your expected output, you may edit your question and provide a sample spreadsheet. Also, please avoid sharing pictures as a reference.