RTV Tools

Friday, January 8, 2016

File Update and Tracking Script

Sometimes you will want to monitor a folder for changes, and possibly export a file list to Excel for consumption in some other format, or perhaps as part of a notification workflow.

I adapted a Powershell script to export a file list using a selected file extension as a filter, and also export some file properties like Directory and LastWriteTime (Date Modified).

Here is the Powershell script:
$arr = @()
gci L:\DIRECTORY -include *.nwc -recurse | ? {$_.PSIsContainer -eq $False} | % {
  $obj = New-Object PSObject
  $obj | Add-Member NoteProperty Directory $_.DirectoryName
  $obj | Add-Member NoteProperty Name $_.Name
  $obj | Add-Member NoteProperty LastWriteTime $_.LastWriteTime
  $arr += $obj
  }
  $arr | Export-CSV -notypeinformation "C:\TARGETFOLDER\LOwnCloudWIP.csv"
Copy-Item
"C:\TARGETFOLDER\LOwnCloudWIP.csv" "C:\Users\Luke Johnson\Google Drive\GOOGLEDRIVERFOLDER\LOwnCloudWIP.csv" -force

Put the above in a text document and save with the .ps1 extension

Quick summary of how it works:
  1.  As it is a powershell script, you will need powershell installed and it will need to be enabled with appropriate permission etc to access the network.
  2. Set up the script as per text above 
  3. Set up Windows task scheduler to call the script periodically. Each time:
    1. Script runs and creates a CSV of filenames and dates etc in the monitored folder
    2. This automatically syncs (using the Copy-Item step) to Google Drive where it is shared with a gd url 
    3. The gd url of the csv file is imported into another Google Spreadsheet, where ARRAY formulas are performed on it to create additional filter parameters based on file name
This 'filterable' Google sheet was shared with an 'lv' key so that it could be easily filtered by recipients. I think the new Google Sheets allow per user filtering, so that step may be unnecessary now.

You may need to set some additional arguments in the Windows task:

The scheduled task looks like this:

    No comments:

    Post a Comment