Pages

Friday, 26 April 2019

Bulk create Google Drive folders

The following Google Apps Script code was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It uses the Module Name and Marker to form the folder name as it iterates through each row in turn. At the same time it takes the Marker Email Address and uses the 'DriveApp' to provide edit access to the folder. Finally it creates another sub-folder in each folder as an extra.

The Folder ID is stored directly in the spreadsheet rather than extracting from the Folder Link via regex (example here) just for simplicity purposes. A confirmatory Yes is placed in the Permissions Added? column to help verify success as the script loops through each row.

There are lots of comments in the code to explain what each line is doing and most of the features have been covered elsewhere in the blog. Remember to add your main Google Drive folder ID into the config sheet where all the sub-folders will be created under.

Bulk create Google Drive folders.xlsx

15 comments:

  1. its working great! Thanks.

    This saved me tons of time.

    ReplyDelete
  2. How do I get this to loop from the latest added row?

    ReplyDelete
    Replies
    1. Hi
      so you mean you run it once, add some more rows, and want it to be able to re-run just for those?

      Delete
    2. I assume you just delete the orginal rows, add new ones and hit run?

      Delete
    3. Hi

      In the example above it was designed to by run once only and then cleared for the next iteration. But you could add an 'if' statement to it which checked that the 'Folder ID' column was blank before proceeding for example, meaning it could be re-run safely.

      Something like this inserted at Line 22 would then only run the rest of the code if true:

      if (data[i][4] == '') {
      // rest of code goes in here ...
      }

      Thanks

      Delete
    4. I had to use if(data[i][4]==null) not if (data[i][4] == '') because my blank spreadsheet cells were undefined not set to an empty string!

      Delete
    5. On a different sheet, however, I had to use if (data[i][4] == '') and the data[i][4]==null didn't work. Any ideas why that might be?

      Delete
    6. Hi

      I'm afraid not - they should be completely empty by default (as you found when creating a new sheet) - it might be worth starting afresh on the new sheet.

      Delete
  3. Bravo, this worked like a charm. Keep up the great work!!!

    ReplyDelete
  4. I don't understand getting the root folder ID from 'config sheet.' I tried to make sense of this and run my code but ran into an error. Any help would be great appreciated! Thank you.

    ReplyDelete
    Replies
    1. Hi

      I implement that because at my place of work non-coders are taking ownership of these spreadsheets/scripts and so I need to make them universally accessible without risking people going into the Apps Script code itself.
      By putting such values into a spreadsheet they can be easily updated by anybody that wishes to use it.

      Delete
  5. I don't understand getting the root folder ID from 'config sheet.' I tried to make sense of this and run my code but ran into an error. Any help would be great appreciated! Thank you.

    ReplyDelete
    Replies
    1. Resolved. You should make a sheet on your spreadsheet and label it config, and in row 1 column 2 put the rootID of the folder you want all your sub-folders to go in. this can be found by going to that folder in drive and copying everything after the last slash (/)

      Delete