Majed Abouhatab, P.E.
Published © LGPL

Google Sheets Scrape Data To Create Hackster Sticker Design

Oh the things I would do to win $100 Adafruit gift card...

BeginnerFull instructions provided1 hour209
Google Sheets Scrape Data To Create Hackster Sticker Design

Things used in this project

Software apps and online services

Google Sheets
Google Sheets
wordart.com

Story

Read more

Schematics

untitled_tmi7oydj8a1_2sFN68fEqs.jpg

Code

Code.gs

JavaScript
function WordList(){
  // We only have one sheet
  var ThisSheet = SpreadsheetApp.getActiveSheet();
  // Start clean
  ThisSheet.getRange("A:B").clear();
  // Look for words and projects here
  var DIRs = ['platforms','topics'];
  // Start at first row
  var ThisRow = 1;
  for (var d in DIRs) {
    // Get HTTP response text
    var AllText = UrlFetchApp.fetch('https://www.hackster.io/channels/' + DIRs[d]).getContentText();
    // Look for h4 tags
    var TextSplit = AllText.split("<h4><");
    // Get data from text parts
    for (var g = 1; g < TextSplit.length; g++) {
      ThisIndex = TextSplit[g].indexOf('\<span\>');
      ThatIndex = TextSplit[g].indexOf(' project',ThisIndex);
      if (ThatIndex > 0) {
        // Word used
        ThisSheet.getRange(ThisRow,1).setValue(TextSplit[g].substring(TextSplit[g].indexOf('\>') + 1,TextSplit[g].indexOf('\<')).replace('&amp;','&').replace('&#39;','\''));
        // Number of projects - used log to compress the range
        ThisSheet.getRange(ThisRow,2).setValue(Math.round(Math.log(TextSplit[g].substring(ThisIndex + 6,ThatIndex).replace(',',''))));
        // Next row
        ThisRow++;
      }
    }
  }
  ThisSheet.getRange("A:B").sort({column: 2, ascending: false});
}

Credits

Majed Abouhatab, P.E.
55 projects • 41 followers
Electronics Professional Engineer, Pilot, Skydiver, Runner, and World Traveler.

Comments