Google Scripting Folder Tricks

Image from page 125 of "The typography of advertisements that pay : how to choose and combine type faces, engravings and all the other mechanical elements of modern advertisement construction" (1917)

I often end up confused about some of the Google Script stuff so I’m putting this out there in case it helps others.

The following script does a few things that you might want to do all based around a Google Form submission. It creates a folder, adds editors to the folder, puts the folder in another folder, and then copies a spreadsheet to the folder while adding some data in a few cells.

function initName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

 // This logs the value in the very last cell of the sheet you used when making the script
 var lastRow = sheet.getLastRow();
 var folder = sheet.getRange(lastRow, 2); //gets the last row and second column
 var init = folder.getValue(); //gets the value of the cell in the last row and second column
 var email = sheet.getRange(lastRow, 10); //gets the last row and tenth column
 var editor = email.getValue(); //gets the value
 
//this chunk is a bit messy but array drama around adding multiple editors see http://stackoverflow.com/questions/11287629/addeditors-to-document-from-formsubmit  
 var emails = sheet.getRange(lastRow, 11);
 var editors = emails.getValue() + ', ' + editor;
 var team = editors.replace(/, /g,"|"); //remove unwanted spaces and commas replace by | 
 var teamArray = team.split('|');
  
   var folder; 
   folder = DriveApp.getFoldersByName(init);    
    if (folder.hasNext()) {
    } else {
      folder = DriveApp.createFolder(init);
      folder.addEditors(teamArray); //adds the array of email addresses from above from two columns as editors
    }
    var parentFolder = DriveApp.getFolderById("thiswastheidbutIremovedit"); //move create folder to parent folder
    parentFolder.addFolder(folder);
    
   var file = DriveApp.getFileById("thiswasanotheridbutIremovedit"); //copies the ss file with the details over to the parent folder
   file.makeCopy(init+' details', folder)  
   
//gets the ss created above and puts it's URL back in the form for reference
     var detailsFile = DriveApp.getFilesByName(init+' details'); //names 
     while (detailsFile.hasNext()) {
     var file = detailsFile.next();
     ssId = file.getId();
     var ssNew = SpreadsheetApp.openById(ssId); //gets the ID of the new SS
     SpreadsheetApp.setActiveSpreadsheet(ssNew);
     ssNew.getRange('B1').setValue(init); //sets cell b1 to the partnership name
     ssNew.getRange('B2').setValue(editor); //sets cell b2 to lead consultant
     
     newSsUrl = ssNew.getUrl(); //get the URL of the details ss

      

      sheet.getRange('O'+lastRow).setValue(newSsUrl); //sets cell O (that's the letter O not zero) in the last row to the details ss URL


 }
  
}




Comments on this post

No comments.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL