I have a google sheet file containing recordings; in particular, column A contains a URL that I want to share with recipients that I enter in columns B and C. I want to put these recipients in the editor of the corresponding URL but without sending a notification.
I try this code:
function partagerFichiers() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Feuille 1");
let data = sheet.getDataRange().getValues();
let fichierUrl, destinataireEd1, destinataireEd2, destinataireCom, destinataireLec;
for (i=1;i<=data.length;i++) {
if (data[i].length > 1 && data[i][0] !== "") { //on récupère la 1ère colonne
fichierUrl = data[i][0];}
if (data[i].length > 1 && data[i][2] !== "") {
destinataireEd1 = data[i][2].split(";").map(function(item) { return item.trim(); });} //on récupère la 3ème colonne
if (data[i].length > 1 && data[i][3] !== "") {
destinataireEd2 = data[i][3].split(";").map(function(item) { return item.trim(); });} //on récupère la 4ème colonne
try {
let fichierId = fichierUrl.replace(/.*\/d\//, '').replace(/\/.*/, '');
let fichier = DriveApp.getFileById(fichierId);
destinataireEd1.forEach(function(mail) { fichier.addEditor(mail); });
destinataireEd1.forEach(destinataireEd1 => {
Drive.Permissions.create({ role: "writer", type: "user", emailAddress: destinataireEd1 }, fichier, { sendNotificationEmail: false });});
destinataireEd2.forEach(function(mail) { fichier.addEditor(mail); });
} catch (error) {
Logger.log("Erreur lors de la récupération du fichier pour l'url: " + fichierUrl);
Logger.log("Erreur :" + error.message);
}
}
}
But I get several errors : "Error: Drive is not defined" and "Error: Invalid argument: permission.value" (for the last one, I think it comes from the fact that I have no return address for certain records )
Thanks
If you want to share files with recipients without them getting an email notification, you can do so by using the Advanced Drive Service Drive API for Google Apps Script. You are on the right track by using Drive.Permissions.create
. In addition, I have tweaked your existing script to make it more readable and efficient, using fewer for loops and conditional if statements.
To begin, you will need to enable the Drive API Advanced Services in your Apps Script editor (this fixes the Error: Drive is not defined
), as shown below:
Note: If you encounter an issue with your actual spreadsheet file with this sample script, it would be greatly appreciated if you could provide a sample sheet from your end so that we can better reproduce your setup.
function shareFiles() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Feuille 1");
let data = sheet.getDataRange().getValues();
const dataToBeProcessed = data.map((row, i) => {
return i != 0 && row.map(cell => {
try {
return new RegExp("d\/(.*)\/", "gm").exec(cell)[1]; //extract file ID from the link
} catch {
return cell.trim().split(";") //split email addresses for recipient 1 / recipient 2
}
})
}).filter(x => x); //filter non-important values
//Process sheet file data
dataToBeProcessed.forEach(data => {
let fileId = data[0];
let setRole = 'reader'; //Set the role here.
try {
data[1].forEach(editor1Recipient => {
editor1Recipient != '' && (setRole == 'writer' ? setAsWriter(setRole, editor1Recipient, fileId) : setAsReader(setRole, editor1Recipient, fileId), console.log(`Successfully shared file ID: (${fileId}) to ${editor1Recipient} with a "${setRole}" role.`))
});
data[2].forEach(editor2Recipient => {
editor2Recipient != '' && (setRole == 'writer' ? setAsWriter(setRole, editor2Recipient, fileId) : setAsReader(setRole, editor2Recipient, fileId), console.log(`Successfully shared file ID: (${fileId}) to ${editor2Recipient} with a "${setRole}" role.`));
});
} catch (e) {
console.log('Encountered an error: \n' + e)
}
})
}
/** Update the file permissions based on 'fileId' for users who have previously been set as 'writers'. */
function setAsReader(setRole, recipient, fileId) {
//Get the permissions from the file.
try {
const pullPermissions = () => {
return Drive.Permissions.list(fileId).permissions.filter(res => res.type == 'user' && res.role != 'owner');
}
const result = pullPermissions().map(permission => Drive.Permissions.update({
role: "reader"
}, fileId, permission.id));
console.log(result); //For backtracking purposes of this log, this contains the number of user shared permssions existing in the file that have been updated.
} catch {//If there are no existing permission for the file with 'reader' privilege, create a 'reader' role for the file.
setAsWriter(setRole, recipient, fileId);
}
}
function setAsWriter(setRole, recipient, fileId){
Drive.Permissions.create({ role: setRole, type: "user", emailAddress: recipient }, fileId, { sendNotificationEmail: true });
}
Sample sheet file:
A sample log result:
The second link (file ID: 1ZR_xvSQG_z-Ow7CKyKM82NEuGIEAYW2y) was not processed due to its blank recipient cells.
Sample file (file ID: 1e6FO9UNX92VYuZVIzyUQ_MClLwc8tMAb):