CODEWITHSUNDEEP
javascriptgoogle-apps-script
Aaron
Aaron

Reputation: 3325

Google apps script to pull text from website

I have a shell script I made to pull the amount of players listed on a game website for example. I took the number and added a timestamp and put it on google spreadsheets so I could make a graph over time to track data. The only problem is to run that, I need to keep my computer on, I'm going to guess you can do this with a google script of some kind but I have no idea how to begin doing that.

The shell script I used was:

 wget --output-document=- http://runescape.com/title.ws 2>/dev/null \
| grep PlayerCount \
| head -1l \
| sed 's/^[^>]*>//' \
| sed "s/currently.*$/$(date '+%m\/%d\/%Y %H:%M:%S')/" \
| cut -d">" -f 3,4 \
| sed 's/<\/span>//' \
| sed 's/,//' \
| awk '{printf "%s %s,%s,%s\n", $3, $4, $1, $2}'

Output example: 11/21/2012 01:02:22,52833,people

What I wanted was to have it auto pull that number and store it on a cell every 2 minutes for example. But I am not sure how easy it is or where to begin.

Then it would look like https://i.sstatic.net/Qp2rJ.png

Upvotes: 2

Views: 6571

Answers (2)

Waqar Ahmad
Waqar Ahmad

Reputation: 3730

You can use UrlFetch App in Apps Script to fetch the content of the page and that can be parsed using regular expressions to get the player count. Here is a little code which I tried. It uses YQL with Apps Script to avoid regular expression parsing.

function retreiveOnlineUsers(){
  var query = 'select * from html where url="http://runescape.com/title.ws" and xpath=\'//div[@id="PlayerCount"]/p[1]/span\'&format=json';
  var yql = 'http://query.yahooapis.com/v1/public/yql?q=';
  var queryURL = yql+encodeURI(query);
  var jsonString = UrlFetchApp.fetch(queryURL).getContentText();
  var json = Utilities.jsonParse(jsonString);
  var palyerCount = json.query.results.span;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  l = sheet.getLastRow();
  sheet.getRange(l+1, 1, 1, 3).setValues([[new Date(), palyerCount, 'people']]);
}

References:

This script is tested and working correctly. Here are the steps to follow to make it working with your spreadsheet.

  1. Create a new empty Spreadsheet and save it with a name
  2. Open the Script Editor by clicking on the 'Tools' menu, then select 'Script editor...'.
  3. Copy and paste the following script
  4. Save the Script
  5. Select the function retreiveOnlineUsers in the function combo box and click "Run", this will populate a row in your sheet.
  6. Now you can schedule this script to run through time driven triggers.

Upvotes: 1

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17792

It's possible that if you may hit a quota limitation when doing this. But here's how I'd do it.

function pullRuneScape() {
  var page = UrlFetchApp.fetch('http://runescape.com/title.ws').getContentText();
  var number = page.match(/PlayerCount.*>([0-9,]+)</)[1];
  SpreadsheetApp.getActive().getSheetByName('Sheet1').appendRow([new Date(), number]);
}

Open a Google Spreadsheet, then click the menu Tools > Script Editor, paste the above code and save. Still on the script editor, click Resources > Current script's triggers. Then set this function to run on a time-driven trigger.

Upvotes: 5

Related Questions