deduplicationgoogle sheetspermissions
I have set up a spreadsheet on Google Drive that multiple users can access and edit, but I also need users to be alerted when the data they enter already exists elsewhere (this could be another sheet, another file etc.) without giving users direct access to the existing data (i.e. users can check if their data is duplicated, but can not view the initial data).
Is this possible on Google Drive?
Maybe this has changed in new Google Sheets because in the second and subsequent Forms it seems possible to select Responses, Choose response destination:
New sheet in an existing spreadsheet..., Choose:
find the Response Sheet for the first Form, Select and for this to work.
The OP basically wants to be able to use the spreadsheet functions over the original data without having to scroll across a large number of rows and columns. His background make him think about having custom views and a easy way to change from one to another but Google Sheets doesn't have a built in feature to manage custom views.
Fortunately the fourth requirements are doable by using built-in features and Google Apps Script
Rows could be hidden by using filters but columns only could be hidden manually or by using code.
For 1, and 2 use Google Apps Script to create custom menus that apply the hide/show columns and optionally rows over the "master sheet". This will allow the user to edit the data directly over the "master sheet" and sort it.
The following code creates a menu for handling the hide/show operations of columns specified in a global variable.
var cols = [1,5,7]; //columns to be hidden/shown
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [
{name: "Hide", functionName: "hideCols"},
{name: "Show", functionName: "showCols"}
];
ss.addMenu("Custom Views", menuEntries);
}
function hideCols() {
var sheet = SpreadsheetApp.getActiveSheet();
cols.forEach( function(col) {
sheet.hideColumns(col);
}
)
}
function showCols() {
var sheet = SpreadsheetApp.getActiveSheet();
cols.forEach( function(col) {
sheet.showColumns(col);
}
)
}
Best Answer
Yes, this is possible using an installable trigger, which runs as the user who created the trigger (not as the user at keyboard). Here is a simple example which compares the content of cell A1 in the current sheet with the content of cell A1 in the sheet "Sheet1" of some master spreadsheet. If they match, the background of A1 is set to red.
After entering this script, add a trigger via Resources > Current Project's trigger. Under Events, select "From spreadsheet", "On edit".
As said above, this means that when triggered, the function
comparewill run as if it's run by you, and have the permissions that you have: in particular, the permission to access the master spreadsheet.