Hello,
I've found this script (below) on the internet. It's for google sheet.
*what it does?*
when I input a value in a cell, script copy the value to a helper sheet.
so, if I or other user tried to delete or change the value, it returns to
previous value, it won't let you change the value.
*what is the problem? what I want?*
*1. *The script creates a helper sheet, so, if other user change the value
from helper sheet it also changes in main sheet. I don't want that. I tried
to lock the helper sheet, then the script won't copy any value from main
sheet to helper sheet. I want to lock the helper sheet only editable by me,
and also other users value copied to helper sheet.
*2. *If I or other user copied bunch of cells and paste to main sheet,
script copy only the first cell to the helper sheet, so except first cell
other cells remain editable.
Hope I've explained properly. Please someone help me on this. Help will be
appreciated.
Thanks
*Script:*
function onEdit() {
// Re:
https://productforums.google.com/d/topic/docs/gnrD6_XtZT0/discussion
//
// This script prevents cells from being updated. When a user edits a
cell on any sheet,
// it is checked against the same cell on a helper sheet, and:
//
// - if the value on the helper sheet is empty, the new value is stored
on both sheets
// - if the value on the helper sheet is not empty, it is copied back
to the cell on
// the source sheet, undoing the change
//
// This in effect protects sheets in a "write once" manner, albeit with
lots of limitations.
// The script does *not* provide protection against edits by a determined
user.
// For example, selecting all cells and pressing Delete lets any user
erase many cells at once.
// The values that were deleted from the cells will however be preserved
on the helper sheet
// and will reappear when an individual cell is edited again.
//
// Helper sheets are created automatically when an edit is first made,
one helper sheet
// per source sheet. For a source sheet named "Sheet1", the helper sheet
is "Sheet1_helper".
// Helper sheets are automatically hidden when created to not clutter the
display, but
// they can be uhidden by any user with "can edit" rights to the
spreadsheet.
// Users with edit rights can also disable this script at will.
//
// To change a value that was entered previously, empty the corresponding
cell on the helper sheet,
// then edit the cell on the source sheet.
// When you rename a source sheet, remember to rename the helper sheet as
well.
// Choose "View > Hidden sheets" to show the helper sheet, then rename it
using the pop-up
// menu at the sheet's tab at the tab bar at bottom of the browser window.
//
// To take this script into use:
//
// - take a backup of your spreadsheet through File > Make a copy
// - select all the text in this script, starting at the "function
onEdit()" line and ending at the last "}"
// - copy the script to the clipboard with Control+C
// - open the spreadsheet where you want to use the function
// - choose Tools > Script editor > Blank (this opens a new tab in the
browser)
// - press Control+A followed by Control+V to paste the script in
// - press Control+S to save the script
// - close the script editor tab and go back to the spreadsheet tab
//
// The script will from then on watch updates on all the sheets and only
allow edits
// when the cell is empty to start with.
//
// Note that the script only protects _values_ rather than _formulas_.
// To protect formulas, use Data > Named and protected ranges.
//
// If your sheets that you would like to protect already have data on
them, create helper
// sheets manually by choosing the Duplicate command from the sheet's tab
menu at the tab bar
// at the bottom of the browser window. Rename the new sheet so that
"Copy of Sheet1" becomes
// "Sheet1_helper".
//
// The range where edits are of this "write once" type can be limited by
changing the values
// assigned to the firstDataRow, lastDataRow, firstDataColumn and
lastDataColumn variables below.
// The range defined by these values is global and will apply to all the
sheets the same.
//
// You can exclude some sheets from being watched by putting them on the
freeToEditSheetNames
// list. See below for more info.
// modify these variables per your requirements
// define the range where edits are "write once"
// to watch only the range A1:D100, define rows as 1,100 and columns as
1,4
// to watch only the range M20:V30, define rows as 20,30 and columns as
13,22
var firstDataRow = 1; // only take into account edits on or below this row
var lastDataRow = 999; // only take into account edits on or above this
row
var firstDataColumn = 1; // only take into account edits on or to the
right of this column
var lastDataColumn = 999; // only take into account edits on or to the
left of this column
// naming pattern for sheets where values are copied for later checking
var helperSheetNameSuffix = "_helper";
// sheets that are free to edit with no protection
var freeToEditSheetNames = ["Free to edit 1", "Free to edit 2",
"Cash_Note", helperSheetNameSuffix + "$"];
// You can use regular expressions in sheet names. The match is not
case-sensitive,
// so "free.*edit" will match "free to edit", "Free Editing for
Everyone",
// "Sheet (free to edit)" and "Free edit playground".
// Leave the last entry, helperSheetNameSuffix + "$", as it is to ensure
that changes to a
// helper sheet do not trigger the creation of another _helper_helper
sheet.
// See these sites for more info:
// - http://en.wikipedia.org/wiki/Regular_expression
// -
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions
var ss = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = ss.getActiveSheet();
var masterSheetName = masterSheet.getName();
var masterCell = masterSheet.getActiveCell();
var sheetNameRegExp;
// do not record edits on free to edit sheets
for (var sheet in freeToEditSheetNames) {
sheetNameRegExp = new RegExp(freeToEditSheetNames[sheet], "i");
if (sheetNameRegExp.test(masterSheetName)) return;
}
// find helper sheet
var helperSheetName = masterSheetName + helperSheetNameSuffix;
var helperSheet = ss.getSheetByName(helperSheetName);
if (helperSheet == null) { // helper sheet does not exist yet, create it
as the last sheet in the spreadsheet
helperSheet = ss.insertSheet(helperSheetName, ss.getNumSheets());
Utilities.sleep(2000); // give time for the new sheet to render before
going back
ss.setActiveSheet(masterSheet);
helperSheet.hideSheet();
ss.setActiveRange(masterCell);
}
if (masterCell.getRow() < firstDataRow || masterCell.getColumn() <
firstDataColumn ||
masterCell.getRow() > lastDataRow || masterCell.getColumn() >
lastDataColumn) return;
var helperCell = helperSheet.getRange(masterCell.getA1Notation());
var newValue = masterCell.getValue();
var oldValue = helperCell.getValue();
if (oldValue == "") {
helperCell.setValue(newValue);
} else {
masterCell.setValue(oldValue);
}
}
--
You received this message because you are subscribed to the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/google-spreadsheets-api/58277a54-8745-4f30-ad53-2f084314d479%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
