crribs.com

This vbscript will retrieve all of the website’s profile numbers for your google analytics account.  Borrowed some code from http://mikaelspage.blogspot.com/2009/08/excel-functions-for-fetching-data.html

Changed a lot of code myself.  Removed some functions there may be remnants of them in there.  It does work though, and seems to be the only example on the web of doing this with VBScript.  Enjoy.

‘**********************************************************************
‘*  Get Google Analytics Profiles
‘************************************************************************

Call Main()

Sub Main()

email = “youremail@gmail.com”
password = “password”
token = getGAauthenticationToken(email, password)

getGAaccountData token, “False”

End Sub

Sub getGAaccountData(authToken, includeHeaders)

If authToken = “Authentication failed” Then
getGAprofiles = “Authentication failed”
WScript.Echo “Authentication failed”
Exit sub
End If
tempFile = “c:\googleAnalytics_Temp.txt”
Set fso = CreateObject(“scripting.filesystemobject”)
If fso.FileExists(tempfile) Then fso.DeleteFile(tempFile)
Set fileTemp = fso.OpenTextFile(tempFile, 8, True)

fileTemp.WriteLine chr(34) & “AccountName”“ , ”“ProfileNumber”“ , ”“ProfileTitle” & chr(34)

URL = “https://www.google.com/analytics/feeds/accounts/default”
Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)

objhttp.Open “GET”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.setRequestHeader “Authorization”, “GoogleLogin Auth=” & authToken
objhttp.send (“”)

gaResponse = objhttp.responseText

If InStr(1, gaResponse, “Token invalid”) > 0 Or InStr(1, gaResponse, “Authorization required”) > 0 Then
getGAprofiles = “Authentication failed”
Exit sub
End If

Set XMLdoc = CreateObject(“MSXML2.DOMDocument”)
XMLdoc.LoadXML(objhttp.responseText)
Set theRoot = XMLdoc.DocumentElement
Set children = theRoot.ChildNodes

rivi = 1
For Each child In children
If child.nodeName = “openSearch:totalResults” Then
riveja = CInt(child.Text)
If includeHeaders = True Then
ReDim TempArray(riveja, 2)
Else
ReDim TempArray(riveja, 2)
End If
End If

If child.nodeName = “entry” Then

Set children2 = child.ChildNodes
For Each child2 In children2
If child2.nodeName = “dxp:property” Then
Set attribuutit = child2.Attributes
If attribuutit.getNamedItem(“name”).Text = “ga:accountName” Then TempArray(rivi, 0) = attribuutit.getNamedItem(“value”).text

End If

If child2.nodeName = “dxp:tableId” Then TempArray(rivi, 2) = CDbl(Replace(child2.Text, “ga:”, “”))

If child2.nodeName = “title” Then TempArray(rivi, 1) = child2.text

Next
fileTemp.Write chr(34) & TempArray(rivi, 0)
fileTemp.Write chr(34) & “ , ” & chr(34) & TempArray(rivi, 2)
fileTemp.Write chr(34) & “ , ” & chr(34) & TempArray(rivi, 1) & chr(34) & vbnewline
rivi = rivi + 1
End If

If includeHeaders = True Then
TempArray(0, 0) = “AccountName”
TempArray(0, 1) = “ProfileTitle”
TempArray(0, 2) = “ProfileNumber”
End If

getGAprofiles = TempArray

Next

End sub

Function getGAauthenticationToken(email, password)
If email = “” Then
getGAauthenticationToken = “”
Exit Function
End If

If password = “” Then
getGAauthenticationToken = “Input password”
Exit Function
End If

CurChr = 1

Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP”)
URL = “https://www.google.com/accounts/ClientLogin”
objhttp.Open “POST”, URL, False
objhttp.setRequestHeader “Content-type”, “application/x-www-form-urlencoded”
objhttp.send (“accountType=GOOGLE&Email=” & email & “&Passwd=” & password & “&service=analytics&Source=InternetEngineKPI”)

authResponse = objhttp.responseText

If InStr(1, authResponse, “BadAuthentication”) = 0 Then
authTokenStart = InStr(1, authResponse, “Auth=”) + 4
authToken = Right(authResponse, Len(authResponse) - authTokenStart)
getGAauthenticationToken = authToken
Else

DTSTaskExecResult_Failure

End If

If Err.Number <> 0 Then
DTSTaskExecResult_Failure
End If
End Function

Function LastDayOFPreviousMonth(aDate)
LastDayOFPreviousMonth = DateAdd(“d”, -1, DateSerial(Year(aDate), Month(aDate), 1))
End Function

Function FirstDayOFPreviousMonth(aDate)
If Month(aDate) > 1 Then
FirstDayOFPreviousMonth = DateSerial(Year(aDate), Month(aDate) - 1, 1)
Else
FirstDayOFPreviousMonth = DateSerial(Year(aDate) - 1, 12, 1)
End If
End Function

Have you ever needed to track the number of pages indexed daily for a site within Google? To save you some time, the following vbscript will grab the number of estimated pages Google returns as a single integer, the same as if you entered: site:crribs.com into the ‘Search’ box. The returned page gives you back – for example – Results 1 – 10 of about 171 from crribs.com. (0.19 seconds) . This script would return the number 171, for analytical/statistical tracking purposes. Make sure to enter the correct cannonical version of your site for the variable if youve specified one in Google Webmaster Tools.

varSite = "crribs.com"

WScript.Echo getGoogIndexedPages(varSite)

Function getGoogIndexedPages(strUrl)

End Function

I was thinking of using Twitter as a kind of ‘syslog’ for all of the scripts we have running (via the task scheduler) all over the enterprise. There is no centralized logging implemented. Ive made some attempts via text files, sql server, the event log, etc., but nothing has taken hold as – easy to implement, universal, and straightforward. This is the visual basic script I came up with to upload the messages.


'**************************************************************************
'* Scriptname: PostToTwitterSimple.vbs
'* TWITTER STATUS UPDATE
'* Brad Shultz - crribs.com
'**************************************************************************
strUsername = "username" 'Username
strPassword = "password" 'Password
strMessage = "This is a test twitter update from a vbscript." 'Message for twitter

Function SendToTwitter(strMessage, strUsername, strPassword)
Set objHTTP = CreateObject("Microsoft.XMLHTTP")
objHTTP.open "POST", "http://twitter.com/statuses/update.xml", false, strUsername, strPassword
objHTTP.send "status=" & strMessage
SendToTwitter = objHTTP.responseText
Set objHTTP = nothing
End Function

This has been something that’s bothered me for awhile- especially at work, with all the tasks I schedule daily.  It bothered me that I always had to look up the system account username and password that’d been designated as our ‘Task Scheduler Account’.  The password is a tough one (and rightly so- its a domain admin account).  It also bothered me that in order to schedule a task, I had to go to the GUI applet interface (Control Panel) or the command prompt, then add the task- even though I’d just written the script and was staring at it in the directory I wanted it to run from.  Thus was born the (fairly ugly, but useful) – ScheduleAsTask context menu item.

There is a limitation on the name of the vbs file- it cannot have spaces in it.  This is because we are using the filename as an argument – so if there is a space in the name, the Windows Scripting Host thinks there are multiple arguments.  This limitation could be overcome by getting the number of arguments found at runtime, and concatenating them fairly easily.

The default name is your script’s name, minus the extension.  If there is already a task scheduled with the same name, the script will throw an error.  You can change the task name to whatever you like in step 2.

And step 3 is just configuring the details for the task.

———————————————————————————

1.  Save the following code to a directory as “schtaskContextMenu.vbs” (the name doesnt matter as long as the extension is .vbs).

2.  Go to http://www.mvps.org/emorcillo/en/code/vb6/index.shtml and scroll down until you see “Using the Task Scheduler”.  Click the link underneath it and save the .dll in the same directory as the script.

3.  Run the script.  You may have to give it permission to run.  Tested on XP Pro sp2.  Might work on Vista, not sure.  Vista uses the TaskScheduler 2.0 interface, which has some backwards support.  Try it.

Here is the script zipped up for you.  Once you have the .dll from the above link in the same folder as the script, simply double click on the script.  It will ask you if you are installing or updating.  Click yes to install the whole shebang, no to just update the script (in case you make any changes), or cancel to, well, cancel the script.

Oh, and be sure to enter the account information in the .vbs so it will work on your machine (yep, its a security risk- anyone could open the script’s source and read the account info- maybe make a special non-admin account for scheduled tasks?  As far as I am concerned – in the environments I work in- the account info is available to any legit user.  If we have a rogue hacker looking through our scripts, plaintext passwords are the least of our concerns.)  Context Menu Task Scheduler Script for Vbscripts

This is an ongoing topic.  Im going to start with the one I use the most.  Pretty much everyday. 

VBSEdit (http://vbsedit.com/)

Pretty much all you need if you do some serious vbscripting.  Right click context menu, run lines or entire scripts and debug them from the editor.  I love the ‘Snippets’ functionality…  why oh why is this not integrated into more text editors (Notepad++, Textpad, ConText, Notepad2, PSPad, Programmers Notepad…come on now….).

Its got an object browser built into it, you can do true debugging (like visual studio – hover your mouse over a variable during runtime to see its current value…). 

And to tie it all together, a single user license (that can be used on multiple machines!!) is only $49.  It always blows my mind when Im looking at code on someone elses computer and they are using notepad.  Its so… masochistic.

vbsedit in action - vbscript ide