Skip to content

Instantly share code, notes, and snippets.

View swbuehler's full-sized avatar

Steven Buehler swbuehler

1 follower · 0 following
View GitHub Profile
@swbuehler
swbuehler / Daily Google Drive Log.gs
Created May 31, 2017 20:48
Email yourself a daily log of updates to your Google Drive (Google Apps Script, requires
/*
Requires the Moment.js library (Resources > Libraries > 15hgNOjKHUG4UtyZl9clqBbl23sDvWMS8pfDJOyIapZk5RBqwL3i-rlCo). Use the latest version.
*/
function myFunction() {
var moment = Moment.load();
var theHTML = '';
var yesterday = moment().startOf('day').subtract(1,'days').format();
var today = moment().startOf('day').format();
var drive = DriveApp.searchFiles('modifiedDate >= "' + yesterday + '" and modifiedDate < "' + today + '"');
theHTML = '<html><p>The following files were updated on Google Drive yesterday, ' + moment(yesterday).format('MMMM D, YYYY') + ':</p><ul>';
@swbuehler
swbuehler / stampPDFs.vba
Created March 13, 2017 17:36
Stamp PDFs using Adobe Acrobat
Sub stampPDFs()
On Error Resume Next
Dim App As CAcroApp
Dim PDDoc As CAcroPDDoc
Dim fso As Scripting.FileSystemObject
Dim jso As Object
Dim i As Long
Dim FileName As String
Dim field As Object
@swbuehler
swbuehler / dailyoutlook.ps1
Created March 13, 2017 16:08
PowerShell - Retrieves and displays daily Outlook appointments.
$olFolderCalendar = 9
$ol = New-Object -ComObject Outlook.Application
$ns = $ol.GetNamespace('MAPI')
$Start = (Get-Date).AddDays(-1).ToShortDateString() + " 00:00"
$End = (Get-Date).AddDays(+1).ToShortDateString() + " 00:00"
$Filter = "[MessageClass]='IPM.Appointment' AND [Start] >= '$Start' AND [End] <= '$End'"
$Appointments = $ns.GetDefaultFolder($olFolderCalendar).Items
$Appointments.IncludeRecurrences = $true
@swbuehler
swbuehler / code.M
Last active September 28, 2016 13:43
Power Query M code to process JSON file from Twitch.tv Import
let
Source = Json.Document(File.Contents("C:\Temp\missellacronin.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"created_at", "notifications", "user"}, {"created_at", "notifications", "user"}),
#"Expanded user" = Table.ExpandRecordColumn(#"Expanded Column1", "user", {"_id", "name", "created_at", "updated_at", "display_name", "logo", "bio", "type"}, {"user._id", "user.name", "user.created_at", "user.updated_at", "user.display_name", "user.logo", "user.bio", "user.type"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded user",{{"created_at", type datetimezone}, {"user.created_at", type datetimezone}, {"user.updated_at", type datetimezone}}),
#"Calculated Local Time" = Table.TransformColumns(#"Changed Type",{{"created_at", DateTimeZone.ToLocal, type datetimezone}, {"user.created_at", DateTimeZone.ToLocal, type datetimezone}, {"user.u
@swbuehler
swbuehler / dedupe.vba
Created September 9, 2016 15:48
Dedupe(): Removes duplicate emails from currently active Outlook folder by comparing the Sent Dates of emails and removing emails with duplicate sent dates.
Sub dedupe()
Dim ol As New Outlook.Application
Dim dates As Collection
Dim mbox As Outlook.Folder
Set dates = New Collection
Set mbox = ol.ActiveExplorer.CurrentFolder
For Each msg In mbox.Items
msgDate = msg.SentOn
@swbuehler
swbuehler / LocalTimeToUTC.vba
Last active September 9, 2016 15:46
Set of types and functions that use the System time to return a value of given date/time in UTC. This is the 64-bit version; to convert to 32-bit search for and remove any occurence of "PtrSafe". I found this on the Internet written by someone else (forget who) but include it here for ease of use.
Public Declare PtrSafe Function SystemTimeToFileTime Lib _
"kernel32" (lpSystemTime As SYSTEMTIME, _
lpFileTime As FILETIME) As Long
Public Declare PtrSafe Function LocalFileTimeToFileTime Lib _
"kernel32" (lpLocalFileTime As FILETIME, _
lpFileTime As FILETIME) As Long
Public Declare PtrSafe Function FileTimeToSystemTime Lib _
"kernel32" (lpFileTime As FILETIME, lpSystemTime _
@swbuehler
swbuehler / CorrectDateOnPage.vba
Last active September 9, 2016 15:42
Changes date of a OneNote page depending on information provided in "Sent" email header from Outlook or a table with "Date/Time" header (VBA). Requires a "LocalTimeToUTC()" function provided elsewhere.
Sub CorrectDateOnPage()
Dim appOneNote As New OneNote.Application
Dim pageXML As New MSXML2.DOMDocument60
Dim pageContent As String
Dim clipboard As New MSForms.DataObject
Dim tNodes As IXMLDOMNodeList
Dim tNode As IXMLDOMNode
thisPage = appOneNote.Windows.CurrentWindow.CurrentPageId
appOneNote.GetPageContent thisPage, pageContent, piAll, xs2013
pageXML.LoadXML pageContent
@swbuehler
swbuehler / RxNorm.vba
Created August 25, 2016 20:32
Get RxNorm drug name based on provided NDC (Office VBA, MSXML2)
Function RxNorm(ByVal strNDC As String)
Dim xmlhttprequest As MSXML2.XMLHTTP
Dim xmlresponse As New MSXML2.DOMDocument60
Set xmlhttprequest = New MSXML2.XMLHTTP
xmlhttprequest.Open "GET", ("https://rxnav.nlm.nih.gov/REST/rxcui?idtype=NDC&id=" & strNDC), False
xmlhttprequest.send
xmlresponse.Load xmlhttprequest.responseXML
If xmlresponse.SelectSingleNode("//rxnormId") Is Nothing Then
RxNorm = "**NDC Not Found**"
Exit Function
@swbuehler
swbuehler / Automatic Trip Log.ps1
Created August 3, 2016 17:47
Fetch trip logs from Automatic API (app registration required)
$results = @()
$headers = @{"Authorization" = "Bearer XXXXXXXXXX"}
$feed = Invoke-RestMethod -Uri "https://api.automatic.com/trip/" -Headers $headers
$next = $feed._metadata.next
while ($next -ne $null){
foreach ($result in $feed.results){
$results += $result
}
$feed = Invoke-RestMethod -Uri $next -Headers $headers
$next = $feed._metadata.next
@swbuehler
swbuehler / importfollowers.sql
Created July 30, 2016 20:06
SQL Server 2016: Stored Procedure to pull data from a JSON text file
create procedure [dbo].[InsertFollowers]
AS
BEGIN
truncate table dbo.followers
INSERT INTO dbo.followers
SELECT k.*
FROM OPENROWSET (BULK 'C:\temp\missellacronin.json', SINGLE_NCLOB) as j
CROSS APPLY OPENJSON(BulkColumn, '$')
WITH (
created_at datetimeoffset,