Creating a linked Google Drive folder for each entry in Tables

[Kiron Chakraborty]

Hi there, 

I'm looking to create an apps script that creates a new sub-folder in Drive every time a new entry is added to Tables, then adds a link to that entry back in tables. I'm guessing its relatively simple but I'm a bit new to apps script so kind of figuring out where to start and how to make this work efficiently.

Thanks for any help!!

[Laurie Nason]

Hi Kiron,

When you talk about "Tables" are you referring to Google Sheets, or something else?

If you are talking about google sheets then here are some starters for you to think about:

1. Are you wanting the sub folder to be created as soon as someone enters a value in a particular column of the sheet (use an on_edit installable trigger), OR
• Can it wait to run on a timer(trigger) where multiple ones could be created (e.g. every hour) OR
• Do you want to run the script to do it when you are ready (create a menu item to do it)
2. What happens if someone edits an already existing value in the same column? Do you want the system to rename the folder, or not?

Then practically speaking I would - 

On the sheet itself:

• have a cell on your sheet at the top which is the root directory URL where you are going to put all the sub directories so you can reference it easily from your script (and also use it to open up the directory easily as well!)
• have at least 2 columns - one for the name (to be typed) and one for the url from the new directory

In the apps script:

• Create a script that will go through your 2 columns (and get the root folder from the sheet)
• Add in the code below to get the ID of a folder/document/sheet from a URL [BONUS!}
• Use this guys help with whichever difficulty level you need to create your folders in your root one
• As you loop through the 2 columns, ignore rows that already have a URL and only create folders for ones with a name but not URL
• Finally - use your range to get the row/cell for the URL and .setValue(newURL) to the newly created folder

Hope this gets you started. There's lots of help out there to get a range of values from a sheet.

Laurie

Bonus code that I use A LOT!!!

// get the ID from a url for use later....

function getIdFromUrl(url) {

return url.match(/[-\w]{25,}/);

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f9336fd4-d2e6-4a7f-a453-cbb009195f86n%40googlegroups.com.

[Kiron Chakraborty]

Hey Laurie,

Thanks for your help! Tables is a new database product from Google that is currently still in Beta - but it is integrated with Apps Script. The cool thing about it is that you can create simple bots to run when something changes in the database which will then execute a task, webhook, or apps script. So no need to code a timer or menu item. 

So I can easily set the bot to trigger when an entry (in this case representing a project) is set from "to do" to "doing", that requires no code. Then I want Apps Script to read the entry name, create a project subfolder for all the files, and then link that folder location back into tables for easy reference. Renaming when entries change would be nice but not necessary if it introduces complexity - as long as the link between entries and drive folders stays constant - thats the main requirement. 

[Laurie Nason]

OK - I did wonder if you were talking about that product... sadly only available in the states at the moment, so no use for the 95% of the world's population who don't live there... :-( 

Have wanted to try it out myself since it appeared, but guess I'll have to wait... and while the scripting part is relatively simple (and relatively agnostic with regard to tables vs sheets) - you'll just have to work out how to trigger and get the information you need to do the creating.

Good luck!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3e54db8c-b601-41a7-8cb8-5b1f423879cbn%40googlegroups.com.

[Chakraborty, Kiron]

Im in the uk! Using a VPN at the moment because its just too useful for my team to ignore.

Thanks for the help Laurie

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/gN3uduyFMJA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWJiakjSyLk%2BFGmM3ia18B9dr7%2BqLCGOV6aAoFauK7PHDQ%40mail.gmail.com.