Make Folders from Spreadsheet Data in Google Drive? -
i beginning learn javascript , google apps script. have looked @ , played few scripts, , attempting create own. want take list of students (name , email) , run script create "dropboxes", or folders uses name , shared email address. way, can submit work me in organized manner. have written rough script, know not work.
i wondering if can give me tips?
function createdropbox () { // current spreadsheet var ss = spreadsheetapp.getactivespreadsheet(); var data = sh1.getdatarange().getvalues(); // each email address (row in spreadsheet), create folder, name data class , name column, share email for(n=1;n<data.length;++n){ var class = sheet.getsheetvalues(n, 1, 1, 1); var name = sheet.getsheetvalues(n, 2, 1, 1); var email = sheet.getsheetvalues(n, 3, 1, 1); var folder = docslist.createfolder('dropbox'); folder.createfolder(class . name); var share = folder.addeditor(email); } }
you've got basic structure of script right, it's semantics , error handling need worked out.
you need determine how want access contents of spreadsheet, , consistent choice.
- in question, first getting contents of spreadsheet two-dimensional array using
range.getvalues()
, , later trying values directly sheet multiple additional times using.getsheetvalues()
. - since algorithm based on working through values in range, use of array going effective approach. reference content of
data
array, need use[row][column]
indexes.
you should think ahead bit. happen in future if need add more student dropboxes? initial algorithm written, new folders created blindly. since google drive allows multiple folders same name, second run of script duplicate existing folders. so, before creating anything, want check if thing exists, , handle appropriately.
general advice: write apps script code in editor, , take advantage of auto-completion & code coloring. avoid mistakes variable name mismatches (ss
vs sh1
).
if going complete exercise yourself, stop reading here!
script
this script has onopen()
function create menu can use within spreadsheet, in addition createdropbox()
function.
the createdropbox()
function create top level "dropbox" folder, if 1 not exist. same students in spreadsheet, creating , sharing sub-folders if don't exist. if add more students spreadsheet, run script again additional folders.
i've included comments explain of tricky bits, free educational service!
/** * create menu item dropbox */ function onopen() { var sheet = spreadsheetapp.getactivespreadsheet(); var entries = [{ name : "create / update dropbox", functionname : "createdropbox" }]; sheet.addmenu("dropbox", entries); }; function createdropbox () { // current spreadsheet var ss = spreadsheetapp.getactivespreadsheet(); var data = ss.getdatarange() // non-blank cells .getvalues() // array of values .splice(1); // remove header line // define column numbers data. array starts @ 0. var class = 0; var name = 1; var email = 2; // create dropbox folder if needed var dropbox = "dropbox"; // top level dropbox folder try { // getfolder throws exception if folder not found. var dropbox = docslist.getfolder(dropbox); } catch (e) { // did not find folder, create dropbox = docslist.createfolder(dropbox); } // each email address (row in spreadsheet), create folder, // name data class , name column, // share email (var i=0; i<data.length; i++){ var class = data[i][class]; var name = data[i][name]; var email = data[i][email]; var foldername = class + ' ' + name; try { var folder = docslist.getfolder(dropbox + '/' + foldername); } catch (e) { folder = dropbox.createfolder(foldername) .addeditor(email); } } }
Comments
Post a Comment