Google script – parse HTML from Website Forum – and Write Data to Sheet

I’m getting HTML from a forum url, and parsing the post count of the user from their profile page. I don’t know how to write the parsed number into the Google spreadsheet.

It should go account by account in column B till last row and update the column A with count.

The script doesn’t give me any errors, but it doesn’t set the retrieved value into the spreadsheet.

    function msg(message){
  Browser.msgBox(message);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Update")
    .addItem('Update Table', 'updatePosts')
    .addToUi();
}

function getPostCount(profileUrl){
  var html = UrlFetchApp.fetch(profileUrl).getContentText();
  var sliced = html.slice(0,html.search('Posts Per Day'));  
  sliced = sliced.slice(sliced.search('<dt>Total Posts</dt>'),sliced.length);
  postCount = sliced.slice(sliced.search("<dd> ")+"<dd> ".length,sliced.search("</dd>"));

  return postCount;
}

function updatePosts(){

  if(arguments[0]===false){
    showAlert = false;
  } else {
    showAlert=true;
  }

  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var accountSheet = spreadSheet.getSheetByName("account-stats");
  var statsLastCol = statsSheet.getLastColumn();
  var accountCount = accountSheet.getLastRow();
  var newValue = 0;
  var oldValue = 0;
  var totalNewPosts = 0;
  for (var i=2; i<=accountCount; i++){ 
    newValue = parseInt(getPostCount(accountSheet.getRange(i, 9).getValue())); 
    oldValue = parseInt(accountSheet.getRange(i, 7).getValue());
    totalNewPosts = totalNewPosts + newValue - oldValue;
    accountSheet.getRange(i, 7).setValue(newValue);    
    statsSheet.getRange(i,statsLastCol).setValue(newValue-todaysValue); 

  }
  if(showAlert==false){
    return 0;
  }
  msg(totalNewPosts+" new post found!");  
}


function valinar(needle, haystack){
  haystack = haystack[0]; 
  for (var i in haystack){

    if(haystack[i]==needle){
      return true;
    } 
  }


  return false;
}

The is the first time I’m doing something like this and working from an example from other site.

I have one more question. In function getPostCount I send the function profileurl. Where do I declare that ?

Answer

Here is how you get the URL out of the spreadsheet:

function getPostCount(profileUrl){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var thisSheet = ss.getSheetByName("List1");

  var getNumberOfRows = thisSheet.getLastRow();
  var urlProfile = "";
  var sliced = "";
  var A_Column = "";
  var arrayIndex = 0;

  var rngA2Bx = thisSheet.getRange(2, 2, getNumberOfRows, 1).getValues();

  for (var i = 2; i < getNumberOfRows + 1; i++) { //Start getting urls from row 2
    //Logger.log('count i: ' + i);

    arrayIndex = i-2;
    urlProfile = rngA2Bx[arrayIndex][0];

    //Logger.log('urlProfile: ' + urlProfile);

    var html = UrlFetchApp.fetch(urlProfile).getContentText();
    sliced = html.slice(0,html.search('Posts Per Day'));

    var postCount = sliced.slice(sliced.search("<dd> ")+"<dd> ".length,sliced.search("</dd>"));
    sliced = sliced.slice(sliced.search('<dt>Total Posts</dt>'),sliced.length);
    postCount = sliced.slice(sliced.search("<dd> ")+"<dd> ".length,sliced.search("</dd>"));

    Logger.log('postCount: ' + postCount);

    A_Column = thisSheet.getRange(i, 1);
    A_Column.setValue(postCount);
  };
}

You’re missing var in front of one of your variables:

postCount = sliced.slice(sliced.search("<dd> ")+"<dd> ".length,sliced.search("</dd>"));

That won’t work. Need to put var in front. var postCount = ....

In this function:

function updatePosts(){

  if(arguments[0]===false){
    showAlert = false;
  } else {
    showAlert=true;
  }

There is no array named arguments anywhere in your code. Where is arguments defined and how is it getting any values put into it?