Linkscape + Google Spreadsheets. Together, at last.

Tweet

First, a brief interlude:

AUUUUUUUUUUUUUUUUUUUGGGGHHH

OK. I’m good now. Primal scream complete.

The documentation for Google App Scripts is… less than perfect. So I have to start by thanking Tom Critchlow of Distilled. He sent me the initial script that made Google Spreadsheets play well with the LinkScape API. Otherwise, I’d be in a small, padded cell right now, rocking gently forward and backward while humming ‘Good Times Bad Times’ and writing doodling with a crayon clutched daintily between my toes.

But, I now have a script that:

1. Fetches linking root domains and domain authority from Linkscape;
2. Finds the right place to insert it into the spreadsheet; and
3. Does it automatically, once a month.

See? Look:

It doesn't look like much, but it brings tears to my eyes every time I look at it.

Here’s how you can do it:

Sure, just copy the code

First off, if you’re such a smarty-pants, you can ignore this post and just copy the code:

See? Nothing to it. If you just giggled hysterically, you may want to keep reading.

Step 1: Store your values somewhere

First things first. You need to put your Linkscape data somewhere easy-to-access. You can just stuff it into your script. But I prefer to put it in the spreadsheet itself. That way, I can change it later on if I need to.

I created a locked tab in my spreadsheet called ‘utility’ and put it there. Then, this code grabs that info for me:

 	  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 	  var utility_sheet = active_spreadsheet.getSheetByName("Utilities"); 	  params = utility_sheet.getRange(5,3,7,3).getValues(); 	  url = params[2][0]; 	  AccessID = params[0][0]; 	  secret = params[1][0]; 

The first three lines go to the “Utilities” tab of my spreadsheet, then grab the values that are stored in rows 5-7, column 3. The last three set my url, AccessID and Secret to the values found in those cells.

Obviously, you have to be careful who gets their grubby paws on your spreadsheet. But remember, you can always generate a new AccessID and Secret if you get paranoid.

Step 2: Generate a secure signature

Linkscape’s API uses a security method that’s probably a cinch for real developers. For me, it made no sense whatsoever. HMAC hash? Sounds like a salty breakfast dish.

But I did finally cobble it together:

 	  method = "HMAC_SHA_1"; 	  uDate = new Date().getTime(); 	  uDate = Math.round(uDate/1000); 	  Expires = uDate + 1200; 	  theString = AccessID + "\n" + Expires;  	  signature = Utilities.computeHmacSignature(Utilities.MacAlgorithm.HMAC_SHA_1,theString,secret); 	  signature64 = Utilities.base64Encode(signature); 	  signature64 = encodeURIComponent(signature64); 

The tricky parts were:

1. Finding the right Utilities code to generate the HMAC hash;
2. Figuring out how to generate a timestamp I could work with;
3. Not punching myself in the face repeatedly.

Lines 1 and 4-8 handle the hash. Lines 2 and 3 generate the timestamp. I can’t write it in any more detail without going post-traumatic. Just take my word for it – it works.

Step 3: Send the request

Once you’ve got the signature, it’s time to send the request:

 	inV="http://lsapi.seomoz.com/linkscape/url-metrics/" + url + "?AccessID=" + AccessID + "&Expires=" + Expires  + "&Signature=" + signature64 + "&Cols=85899345920"; 	jsonStringResponse = UrlFetchApp.fetch(inV);    

That’s it. That code creates the URL (line 1) and then sends the request to Linkscape (line 2). It stores it in the variable jsonStringResponse.

Step 4: Figure out what you’ve got

Next, the script parses the response, which is delivered in JSON format. Google Apps Script has a great built in function called jsonParse. That does the whole job in line 2:

 	var data1 = jsonStringResponse.getContentText(); 	data2 = Utilities.jsonParse(data1); 	IDomains = data2["fipl"]; 	Dauth = data2["pda"]; 

Then it reads in the linking domains and domain authority, which have the variable names fipl and pda when delivered from Linkscape.

Step 5: Do something with it

Finally, time to insert the data into the spreadsheet. This would’ve been a headache, except for this great little function, which finds the last row containing data.

So, the first two lines of the script are:

 	lastRow = FindRows(); 	startRow = lastRow + 1; 

That’s it. It finds the last row with data, then sets the start row to the next. Just be sure you grab the FindRows() code here, too!

Then the script sets the values of the cells accordingly:

 	SpreadsheetApp.getActiveSheet().getRange(startRow,3).setValue(IDomains); 	SpreadsheetApp.getActiveSheet().getRange(startRow,4).setValue(Dauth); 	monthYear =  utility_sheet.getRange(12,4).getValue(); 	today = monthYear; 	SpreadsheetApp.getActiveSheet().getRange(startRow,2).setValue(today); 	SpreadsheetApp.getActiveSheet().getRange(4,3).setValue(today); 

And, finally, adds a nice black outline to the table itself. This loops through columns 2-12, outlining each box with black border:

 	for (z=2; z<12; z++) 		{ 			SpreadsheetApp.getActiveSheet().getRange(startRow,z).setBorder(true,true,true,true,true,true); 		} 

I have other data I’m storing in this table—Blekko inlinks, for example—so I format the whole row.

Step 6: Set a trigger

One last step: You have to set a trigger to fire the script when you need it. Linkscape’s database updates every few weeks, so I don’t need to hit it every day.

I’m not going to write all the script out here, but basically I:

1. Wrote a script that checks if the it’s the first day of the month. If it is, then it fires the getLinkscape script.
2. Set a nightly trigger to run the date checker. Don’t worry, setting up a trigger is point-and-click easiness.

There you have it

There you go: One Linkscape data grabber thingamabobber.

I wrote this as part of my ongoing quest for the perfect internet marketing dashboard.

Challenges I still face:

1. Google’s charting capabilities are awful.
2. The social media side of the sheet is buggier than Nova Scotia in August.
3. I may have lost last, tiny grip on sanity.

Other stuff

• Why all web developers should learn SEO
• Why SEOs should learn programming
• Dan Cobley: What Physics teaches about marketing
• 1 tip for faster blogging: Use Textile
• Analytics plus SEO equals content
• The internet marketer’s guide to the apocalypse
• Follow me on Twitter
• The Real World Unscary Guide to SEO Copywriting

Source: http://feedproxy.google.com/~r/conversationmarketing/MRJI/~3/rb5WamNAH8U/linkscape-google-spreadsheets.htm

internet marketing search engine search engine marketing seo shelly long len lesser sem