GAS Folders, Sub Folders and their Files

Google Drive is a wonderful thing, but when it comes to getting at a file with scripts things can get a little quirky. Every file and folder has a unique ID number (28 digits, letters and other marks) which can make it easy to locate it, if you know how. However you can of course have many files, or folders with the same name spread out across your Google Drive, which can create a bit of a problem when trying to find the file or folder. Very often you may have to iterate over “all the folders” in order to find what you want, using names to do this can be problematic.

I find I am routinely needing to grab a list of filenames, file IDs, or file types, and routinely searching the web each time I need to do this, therefore it was about time I tied down the required scripts in one place for reuse. The scripts I have put together will request either a folder name or ID in order to iterate over the files within that folder and return the results to a spreadsheet. The scripts can be used as a base, and edited to suit specific requirements by adding further properties to return, or through the use of if statements to narrow down selected files.

  • You can find the ID of the folder by looking in the url address bar if you have the folder open in Google Drive
  • You can also right click on the folder and then Shareable Link
  • Finding the name should be obvious! – use search in Google Drive to check for folders with the same name. It may be wise to do this occasionally if you use name a lot in your scripts, and to make your folder names unique

In my scripts I have chosen to return the filename, the mime type, the file ID, and to create a download link. I also include a helper script (courtesy of Sandy Good) that presents the mimetype in a more user friendly way. All four folder scripts rely on this. Thanks should also go to Brian P and AD:AM on the google group support forum for their code.

I will add a link to a google sheet which contains all the scripts, just make a copy of it to your own google drive, authorise the scripts and away you go.

Continue reading

GAS Import CSV from GMail Attachment

I have a weekly email sent to me with a csv attachment. This needs to be opened and the contents pasted into a google sheet, the data therein is then used for a variety of other applications running on Google sheets. Fortunately there is a way to automate this and to parse the csv to sheets. I have robbed and amended the code provided by Digital Inspiration to do this. The linked page also includes other examples of scraping csv data from an email attachment. The key to getting this to work is a consistent approach by my email sender, who needs to use the same email address and subject in order that the GMail filter I set up can assign a label to the email. The script is run from the receiving google sheet on a timed trigger, once a week.

The sheet needed sorting to meet requirements of others. Above code kept nice and short thanks to the parsing API provided by Google. Of course, this will work for a single user google account, calling on the gmail of that user. Because I have G Suite it is simple to create a separate account to handle this type of thing.

 

GAS – Three Crackers: 1. Insert image at specific place in a table, 2. Remove extra paragraphs created by 1, 3. Proportionally size your image

The code, used on a google sheet to add an image to a specific location in a google doc table, should be self explanatory! I use this when an image is needed in a spreadsheet data to google doc to pdf creation exercise (hmmm – had better post that up too 😉 Continue reading

AppInventor – Create Dynamic First List for Listpicker

Following on from my recent post on Dynamic Lists for Listpickers, I ventured a bit further.

Well I now have a rough version (and it is ugly!) that creates a dynamic list for the first list in a nested listpicker. This assumes a list of names or words in the master list, and that these are sorted alphabetically. The issue with creating such a thing is that, say for names, there are 26 letters that names could start with, but that the distribution of names is very uneven. In my sample list 3 letters took over 30% of the entire list. My aim was to present nested lists that could all be viewed without scrolling (between 10 and 12 items), but because of the above this is just not possible all the time. I only have the blocks here that create the first list for the listpicker, the A-D,E-H,etc. as the blocks probably need some optimisation before writing the whole thing out. But here goes: Continue reading

Google Sheets – ArrayFormula with Header/s

The array formula is a wonderful thing in Google Sheets, in most cases providing the opportunity to enter just the one formula and have it automatically fill down as new entries appear from elsewhere.

A clever trick though is to create an array within an array, which allows for header rows to be included.

Continue reading

GAS – Google Sheets – Dynamic Menu and Functions for Sheet Selection

May seem like a sledgehammer to crack a nut, but I was looking for a way to satisfy two things:

  1. Change sheet tabs and know (script-wise) which tab I had come from and was going to
  2. Create a dynamic menu for sheet tab selection, so that I could get the user focused on a single tab, as opposed to having them all there along the bottom

I first tackled this the hard coded long winded way, capturing the names of all sheets and writing an individual function for each, then moved on the a pair forwards / backwards functions, using “getSheets()” to provide the sheet array. This then lead me to going all dynamic!

Started searching, and there were a couple of posts about placing parameters into custom menu items, but nothing that really cracked it, until I came upon this post:

http://clav.cz/google-apps-script-menu-functions-with-parameters/

where Václav Novotný provides a solution to create a dynamic custom menu, and dynamic scripting for functions to go with it. This was setup for a Google Doc, so I need to rework it a bit for my needs. All credit to Václav Novotný though for the meat on the bones. Continue reading

GAS – Insert Checkmark to Cell and Conditional Formatting

A little timesaver. Google Sheets doesn’t have a “Special Characters” selector, so if you want to use one you have to open up a Google Doc, copy the character you want, then back to Sheets to use it. This example shows a simple apps script for inserting a checkmark to a cell. We will then look at conditional formatting of the cell/cells. Continue reading