Google-sheets – Google Sheets sharing based on dynamic list

google sheetsgoogle-drivegoogle-drive-sharing

I'm betting this is way beyond Sheets' capabilities, but my workgroup is large and changes frequently. It would be ideal to be able to share Sheets documents based on a column of values in another (or even the same) sheet.

For example,

Column A   B
       x   ashley@blah.com
           bob@blah.com
       x   charlie@blah.com

A check of column A would yield 'ashley' and 'charlie' for the sharing list.

Even remotely possible?

Best Answer

The best way to handle sharing files with large and dynamic groups on Google Drive is by using Google Groups, but it's also possible to do the way you are thinking by using Google Apps Script or the Google Drive API and the Google Sheets API

To use a group from Google Groups to handle sharing files, share the files with the group by adding its email address.

To use Google Apps Script you have to create a Google Apps Script project, add the code and run it. This could be done directly from the Google Apps Script editor, by using an inserted drawing as button, using a custom menu, sidebar or dialog.

To use Google Drive API and Google Sheets API you should write program by using a programming language that support calling APIs

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Google-sheets – Copying data to and from another sheet based on the month of a date

From the question

Is it possible for creating the subset (filtered by Month) in a way that if we were to make changes in that subset view, those changes would cascade back to the Master. Would QUERY work? Or do I have to resort to a script. Or it is not possible.

Short answer

Google Sheets doesn't have a built-in feature that syncs two spreadsheets so you have to use something that extends it.

Explanation

Built-in functions like QUERY are not able to make make changes to another range. They could only return a value or an array of values. The same apply to custom functions.

Below is included a very simple Google Apps Script code example that exports the value of an edited cell to a cell of another spreadsheet. That code could be added to two spreadsheets to export the edited cells values to each other in order to have a "two way sync" between those spreadsheets.

It's purpose is to show that it's possible to sync two spreadsheets by using Google Apps Script. In order to make it work to sync a master sheet with filtered sheet to logic to find which rows are the match in the other file should be added. Usually this is done using an unique ID.

Broad instructions

Copy the code to script projects bounded to the each spreadsheet and update the targetID and targetSheetName variable definition code lines (lines 1 and 2).
Add a On Edit installable triggers to each script project.

Code

var targetID = 'spreadsheet-id';
var targetSheetName = 'Sheet1';

function onEdit(e) {
  // Get the event object properties
  var range = e.range;
  var value = e.value;
  //Get the cell position 
  var row = range.getRowIndex();
  var column = range.getColumnIndex();
  exportValue(row,column,value)
}

function exportValue(row,column,value) {
  var ss = SpreadsheetApp.openById(targetID);
  var s = ss.getSheetByName(targetSheetName); 
  var target = s.getRange(row, column);
  target.setValue(value);
}

Limit the changes to be synced

In order to limit the changes to be synced add rules to the code. The JavaScript comparators ==, ===, !=, !==, >, <, >=, <=, and the logical operators &&, ||, ! with if, switch among other commands could be used to create simple of complex rules.

Best Answer

Related Solutions