google sheets - Tether workbook copies to a master copy. Updates to copies filtered according to a key - Stack Overflow

admin管理员组

文章数量:1305095

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

• google-sheets
• google-apps-script
• google-sheets-formula
• google-apps

Share Improve this question Follow edited Feb 5 at 22:18 Iselee Hill asked Feb 3 at 23:09 Iselee HillIselee Hill 122 bronze badges 3

• Hi and Welcome to Stackoverflow. Please provide a minimal reproducible example like markdown tables containing your sample input data. Also include what are things you have tried and putting some sample data would help to know what are things to be done so they can be easily read and tweaked by editors, and this then updates the entry in the spreadsheet. – EL SRY Commented Feb 4 at 4:18
• Hi, I added a link to a small sample. If this is too simple I can add more detail. – Iselee Hill Commented Feb 4 at 18:20
• Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented Feb 5 at 9:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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.

本文标签：