How do you save a copy of the current Sheets file to a read-only copy? We have scripts that set values that once confirmed save to a new Sheets file. This new file needs to be set as ReadOnly as a part of the script.
3 Answers
In your situation, how about using content protection?
Sample script:
Before you use this script, please enable Drive API at Advanced Google services.
const fileId = "###"; // Please set the file ID. In your situation, please set the Spreadsheet ID.
Drive.Files.patch({contentRestrictions: [{readOnly: true}]}, fileId);
- When this script is run, the Spreadsheet can be only viewed and the Spreadsheet cannot be written.
- If you want to unprotect it, please modify
{readOnly: true}to{readOnly: false}in the above script.
References:
1 Comment
justdan0227
Thanks. That might work but wanted to save a copy and have it protected. Came up with an alt solution. Just saved it to a PDF! :)
A Google Sheets file will always be editable by the file onwer
It will not accesiable by other users it all unless it is explicitly shared with them -The sharing can take place either through the UI or by script
To do it by script, you can use the method Spreadsheet.addViewer() or File.addViewer()
If a user who has is already an editor needs to be "downgraded" to a viewer, you can do remove him from the editors with removeEditor() before adding him as a viewer
You can also remove all editors (apart from the spreadsheet owner), sample:
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getEditors().forEach(function(editor){
ss.removeEditor(editor):
})
5 Comments
Tanaike
I thought that
+ of var ss + SpreadsheetApp.getActiveSpreadsheet(); might be a mistype.
ziganotschka
Thank you @Tanaike! I have the
+ and the = on the same key :-)Tanaike
Wow! It's your custom keyboard? That's nice!
ziganotschka
Unfortunately no @Tanaike, it is a common keyboard layout for US/UK keyboards. But it's an interesting idea about getting one's own custom keyboard :-)
Tanaike
Thank you for replying. That is new information for me. In the Japanese keyboard I'm using,
- and = are the same. And, ; and + are the same. I sometimes mistype them.You can use the DriveApp Permission enum, .setSharing, and related methods to do this.
Assuming you have a reference to the File object represented by the new Sheets file, this function will change all sharing so that the file is "View only" for anyone who previously had edit permission.
/**
* Change sharing permissions on a file or folder.
* @param {File or Folder object} asset
*/
function makeViewOnly(asset)
{
// General sharing permissions: change VIEW to NONE if you only want specific people to view the file
asset.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
asset.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
// These only apply if the file is on a Workspace domain.
asset.setSharing(DriveApp.Access.DOMAIN, DriveApp.Permission.VIEW);
asset.setSharing(DriveApp.Access.DOMAIN_WITH_LINK, DriveApp.Permission.VIEW);
// Change all edit permissions to view permissions
users = asset.getEditors();
user.forEach(function (user) {
asset.removeEditor(user)
asset.addViewer(user)
});
}
If you have a reference to the Spreadsheet object for the newly created Sheets file instead of the File object, you can get the File object via
var asset = DriveApp.getFileById(spreadsheet.getId())
