Skip to content

Instantly share code, notes, and snippets.

@bennettscience
Last active October 31, 2019 15:30
Show Gist options
  • Save bennettscience/4cc6f3fd1dc7f06d946916735296b539 to your computer and use it in GitHub Desktop.
Save bennettscience/4cc6f3fd1dc7f06d946916735296b539 to your computer and use it in GitHub Desktop.
Automatically set a programmatically-created Google Doc to the correct owner.
/*********** SETUP ***************/
// Store two global variables with columns to use as references.
// These should be zero-based because they're used in a loop.
var docIdCol = 0 // Int of col to check for a document ID
var emailCol = 0 // Int of col with submitter email address
/********** END SETUP **************/
function setDocOwner() {
var doc, isEmail, isSet, newOwner
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('your-sheet');
var lastCol = sheet.getLastColumn();
var rows = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
// Use regex to check that emailCol is actually an email address
var re = new RegExp('[A-Za-z0-9_%+-]+@[A-Za-z0-9.-]+','i');
// Looping from the bottom runs faster
for(var i = rows.length -1; i>=0; i--) {
isEmail = re.exec(rows[i][emailCol])
isSet = rows[i][lastCol];
if(!isSet && isEmail) {
doc = DriveApp.getFileById(rows[i][docIdCol])
if(doc.getOwner !== newOwner) {
trySet(doc, newOwner, lastCol, i+1);
}
}
}
}
function trySet(doc, email, lastCol, row) {
try {
doc.setOwner(email)
sheet.getRange(row, lastCol).setValue('Set owner as ' + email);
} catch(e) {
sheet.getRange(row, lastCol).setValue(e.message)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment