Writing Scripts for Google Drive

Thanks to tips from John Sonmez’s book “Soft Skills: The Software Developer’s Life Manual”, I write a weekly report to my supervisor.  This is great for my career on so many levels. My supervisor sees my progress on my work and also I have a record kept for annual reviews. I write my weekly reports in Google Docs, but it was becoming really repetitive and a time sink to write them up and keep them organized. So I wrote a script to help me with that.

The Google Apps Scripts are based on Javascript so it’s pretty easy to pick up and use. You can read more about Google Apps Scripts and getting started in the links below.

Here’s the format that I was aiming for:


Here’s the code:
Link to Pastebin

Folder ID Numbers can be located by traversing to the folder in Google Drive and then look at the URL: https://drive.google.com/drive/folders/FolderID#Here

I’m not a huge fan of Javascript so forgive me if its kind of ugly looking. It works so I’m satisfied with it in general. Okay so here’s what’s happening in the code:

  1. Create the document
  2. Get the date and rename the document
  3. Loop through and write weekdays into the document body and add bullet points below each day
  4. Copy the file to desired folder destination
  5. Delete old file in root folder (there is no built-in “move function” in Google App Script yet.)

Hope you found this helpful. Send me questions or comments to hey@charlesbickel.com or Tweet at me @charles_bickel


// Slice dates give double digit mm & dd
function sliceit(x) {
thing = ("0" + (x)).slice(-2);
return thing
}

// Get Monday
function dateDiff() {
var day = d.getDay(),
diff = d.getDate() - day + (day == 0 ? -6:1); // adjust when day is sunday
return diff
}

// Get date & create name of document
function setName() {
d = new Date();
diff = dateDiff(d);
thatmonday = new Date(d.setDate(diff));
thatfriday = new Date(d.setDate(thatmonday.getDate() + 4));
newformat = 'Completed ' + thatmonday.getFullYear() + '_' + sliceit(thatmonday.getMonth() + 1) + '/' +
sliceit(thatmonday.getDate()) + ' - ' + sliceit(thatfriday.getMonth() + 1) + '/' + sliceit(thatfriday.getDate())
return newformat
}

// Write each day of the week in document
function writeDays(step) {
d = new Date();
var days = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
eachday = new Date(d.setDate(diff + step));
eachdayformat = days[eachday.getDay()] + ' ' + sliceit((eachday.getMonth() + 1)) + '/' + sliceit(eachday.getDate()) + ':';
return eachdayformat
}

// Move from root to work folder
function moveFile(file, folder) {
folder.addFile(file);
DriveApp.getRootFolder().removeFile(file);
}

function myFunction() {
// Create document
var folder = DriveApp.getFolderById("EnterYourFolderIDNumberHere");
var doc = DocumentApp.create(setName());
var file = DriveApp.getFileById(doc.getId());
var body = doc.getBody();

// For loop write days of the week and assign bullet points
for (step = 0; step < 5; step++){
body.appendParagraph(writeDays(step));
body.appendListItem("\n").setGlyphType(DocumentApp.GlyphType.BULLET);
}

moveFile(file, folder);
}

Microsoft Task Scheduler is weird and does weird things…

I find the Microsoft Task Scheduler useful for running scripts on Microsoft. It’s Microsoft’s “cron”. I’ve used it for multiple projects, but there’s something you should be fully aware of… scripts may give a failure code of 0x1 and the scheduler will mark the task as successful.

This happened to me before. I decided to check on my task to see how it had be running and wanted to see about any hiccups. Here the program had been failing for a decent period of time. I thought the trigger event I set in scheduler would have altered me via email, but it never sent the email! Why would this happen?

After some searching I see that some parameters can determine this outcome. For instance this can happen if “Run with highest privileges” is not selected. Others reported that it would only work properly if they selected “Run only when user is logged on”. Task scheduler does this to Python programs that fail. Even if they fail miserably!

Ironically, my little test gave the proper exit codes. I had more challenges when I first ran into this. My point is that small changes in the task scheduler settings can radically change the outcome and it’s not easy to narrow down the cause.

Set a backup notification! Task Scheduler doesn’t seem to have a very robust way to set conditions for notification. You could track down an event id from Event Viewer and then set another task to watch for that event id to pop up and then email you. However, that’s kind of a pain and it’s a confusing mess with which id could be generated as well as trying to be proactive about the problem. Take a look at my first post about calling a PowerShell script to send an email. You could send a text via Twilio. Make a little notification module that you can tack on to your Python programs. Get creative, test out some solutions, and share your experience with me!

Wonderment of Regular Expressions

Regular Expressions are awesome!…And useful.

I first learned about regular expressions when I started learning programming. I didn’t immediately understand the usefulness and power of regular expressions. They were confusing and intimidating. MSDN.Microsoft.com states that “A regular expression is a pattern that the regular expression engine attempts to match in input text”. Basically, regular expressions (often shortened to RegEx) are used for pattern matching and become invaluable for string searches. I’ve been using regular expressions with Python. However, regular expressions can be utilized by multiple programming languages and may differ slightly between languages. They’re basically concise magic, like an ancient art that has been rediscovered from obscurity. As you can tell, I’ve come to really love regular expressions. They’re powerful and underestimated.

Let’s look at some common uses and examples:

 

Phone numbers!

\d\d\d-\d\d\d-\d\d\d\d

\d stands for any digit 0-9. So this will only accept the match in this format 555-555-5555.

\d{3}-\d{3}-\d{4}

This one is identical to the one above. Curly brackets { } signify a quantity repeating. In this case \d any digit repeats {3} times, etc.

\(??(\d{3})\)??[-\.\s]??(\d{3})[-\.\s]??(\d{4})

This one will accept numbers in different formats. IE: (555)-555-5555, 555-555-5555, 5555555555, 555.555.5555. You get the idea. It handles many formats. ?? is a quantifier matching between 0 and 1 times. This means something that is ?? may or may not be part of the matching. Just like the parentheses for the area code. Some people add those in and others exclude them. Also something important to note, the back slash means the next character will be taken literally. This is present at the very beginning of the regular expression which means this first parentheses is not a grouping, but I’m telling regex to literally keep an eye out for that first parentheses as part of my match. This is also applied to other symbols that I want to be taken as literal.

Email!
Perhaps you’re looking for email addresses or phone numbers on a long page of text. We know that an email address follows a basic pattern. “Something @ something .com” I looked at a lot of different Regex for email and I didn’t like most of them. This one fits most criteria.

([_a-zA-Z0-9-.+]+)*@([_a-zA-Z0-9-.+]+)*(\.[a-zA-Z]{2,4})

Okay, I'll try to quickly go through this crazy looking thing. Parentheses are groupings. All of the stuff between [] means it'll accept any of the characters lower case a-z, capital A-Z, numbers 0-9, and the symbols (_-.+). + is a quantifier that matches between 1 and infinite number of matches. * is a quantifier that matches between 0 and infinite number of times.

Let’s look at times I’ve used these in my projects

Searching for a specific word or set of words:

Grouping can come in handy when you want to search for groups of words. Parentheses ( ) signify a grouping.

In this program, I was searching a network for specific models of Cisco IP phones. I use a regular expression to find splash pages with “ip phone” somewhere on the page.

Link to lines in project.

verify_cisco = re.compile(r'(ip phone)', re.I)
mo = verify_cisco.search(r.text)
print('Cisco ' + mo.group())

My regular expressions are often just basic word searches. In this one you see ‘re.I’. That’s a flag parameter for the expression to ignore case sensitivity. Remember programs and computers aren’t smart and they’ll only do what you tell them to do! You have to think in a precise manner. In Python, Regex matches return “match objects”. The matching object has a group() method that contains the text or whatever you were searching for. Let’s quickly delineate the differences between re.match, re.search, and re.findall.

re.match() will only match if 0 or more characters match at the beginning of the string.

re.match(pattern, string, flags=0)

re.match(“sea”, “Sally sells sea shells by the shiny sea shore”).group() = FAIL
re.match(“Sally”, “Sally sells sea shells by the shiny sea shore”).group() = SUCCESS
Returns “Sally”

re.search() will match for the first and only the first successful match it comes across in string.

re.search(pattern, string, flags=0)

re.search(“sea”, “Sally sells sea shells by the shiny sea shore”).group() = SUCCESS
Returns “sea”

re.findall() will return a list of groups that match.

re.findall(pattern, string, flags=0)

re.findall(“sea”, “Sally sells sea shells by the shiny sea shore”)
Returns [“sea”, “sea”]

If you want to search for one match from multiple words. The | operator functions as “or”.

Link to lines in project.

typeRegex = re.compile(r'(7942G|7962G|7911G|7925G)')\
mo2 = typeRegex.search(r.text)

Here I’m performing a similar search, but in the grouping I use the “or” operator to signify a match if one of the patterns is present. I was looking for any of these phone models so if the text I was looking at contained one of these then the match was successful.

Keep in mind unsuccessful matches return a None value or null value. If you attempt to put the .group() method at the end of the search for re.match or re.search on an unsuccessful match you’ll get an error message. This is because you’re attempting to call a method on a None value that has no methods. Remember the match object has the .group() method and you’ll have to catch those kind of errors.

These are some easy RegEx searches. They obviously get much more complicated, but I’ll leave that for another post. I’ll leave some good regular expression resources below.

https://regex101.com/#python
http://pythex.org/
https://www.debuggex.com/
http://regexr.com/
https://regexcrossword.com/
http://regexone.com/
https://regex.netlify.com/cheat-sheet

 

I use Regex101 quite a bit. They have a unit test section that comes in really handy if you’re trying to test a regular expression and you’ll have a lot of possibilities you want to test against. Instead of rewriting them over and over you can just save the test and test the regexes in batches. It’s pretty great! Here’s a screenshot from the my tests of the phone number example above.

 

I really liked “Automate the Boring Stuff with Python” from Al Sweigart. He has a great chapter in his book about regular expressions that helps shed light and understanding on the subject. Link to the chapter. Be sure to support the author.

If you have some good resources for regular expressions, if you want to share a project you used them in, if you have any suggestions, or if you have any questions; send me an email! Thanks for reading.

Using Python and Powershell to email within Windows Environment

I was working on a project to perform some logic and then send an email (from a Windows environment) when certain conditions were met. Here are a couple things I ran into.

  1. I had a hard time finding useful information on sending email via Python and Exchange.
  2. I didn’t want to store my credentials anywhere.

I decided to use Powershell to send the email. The largest benefit was that I could send an email via Powershell on behalf of certain accounts without credentials. This comes in handy if you are using a noreply address to send out reports or notifications. Here’s what the Powershell script looked like to send the email.

$To = "First Last <first.last@email.com>"
$From = "FromAddress <from.address@email.com>"
$Cc = "First Last <first.last@email.com>"
$Subject = "Outdated Reports on Server"
$body = @"
Detected outdated reports:
\\Server\Folder\ "outdated_reports.txt"

This script originates from \\Server\ScriptsFolder
"@

Send-MailMessage -To $To  -From $From -Cc $Cc -Subject $Subject -body $body -SmtpServer "emailserver.domain.com"

Link to Github

You can use Python to call cmd commands or even Powershell scripts. Here I use subprocess.Popen to perform this task.

p = subprocess.Popen(['powershell.exe', '-ExecutionPolicy', 'ByPass', r'C:\Scripts\SendEmail.ps1'])
p.communicate()

Link to Github

Notice this… “-ExecutionPolicy Bypass” I ended up having to use this switch to launch the script from a server. The server had strict execution policies set. Ideally, you want to figure out how to sign your scripts and configure your server to execute signed scripts. However, it’s kind of a pain and I was in a hurry.

(This seems like a huge security flaw. “Ummmmm yeahhhhh… I’m gonna need you to bypass the security policies… and let me execute some code that would be great… M’kay?”)

Checkout the docs to learn more about the subprocess module and Popen.communicate.

This particular program had to be run daily so I just used Task Scheduler to run it every morning. If this was helpful to you, send me a tweet on twitter or send me an email.