CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptweb-scraping
Sean Watson
Sean Watson

Reputation: 13

Trying to get data from this site into google sheets using app script

Could anyone help me with the code to convert responses from this site https://services.sia.homeoffice.gov.uk/PublicRegister/

I need it to read a license number from google sheets - example: 1012894854698842

And populate results into the same spreadsheet

I've been playing around with both postman and website scrapers but still can't figure it out

function senddatatoform() {

var url =   'https://services.sia.homeoffice.gov.uk/PublicRegister/SearchPublicRegisterByLicence'; // this is the 'request url' as shown in your browser (not always the url of the form).  

var payload = {
 LicenseNo: "1012894854698842",
 
}//continue with all required post data   

var options = {
method: "POST",
payload: payload
}

var response = UrlFetchApp.fetch(url,options);
/*this is where what you need to do next might vary -- if you're looking for a 'success' page, you might write some code here to verify that the http response is correct based on your needs.
*/ 

const content = response.getContentText();

const regEx = /<div class="ax_h5">\s*(\S+)/g; 
while (match = regEx.exec(content)) {
console.log(match[1]);
}

};
Logger.log

The Information I require is

First name

Surname

Licence number

Role

Licence sector

Expiry date

Status

Status explanation

Additional licence conditions

The license number is in a spreadsheet for each employee

Upvotes: 0

Views: 98

Answers (2)

idfurw
idfurw

Reputation: 5862

enter image description here

function senddatatoform() {
  const colId = 1;
  const colOutput = 2;
  const rowStart = 2;
  const cols = 9;
  
  const sheet = SpreadsheetApp.getActiveSheet();
  const rows = sheet.getLastRow() - rowStart + 1;
  const ids = sheet.getRange(rowStart, colId, rows, 1).getValues();
  const values = new Array(rows);
  for (let i = 0; i < ids.length; i++) {
    const data = getData(ids[i][0]);
    if (!data) { values[i] = new Array(cols); }
    else { values[i] = data; }
  }
  
  sheet.getRange(rowStart, colOutput, rows, cols).setValues(values);
  
  function getData(id) {
    const url = 'https://services.sia.homeoffice.gov.uk/PublicRegister/SearchPublicRegisterByLicence';
    const payload = {
      'LicenseNo': id,
    };
    var options = {
      'method': 'POST',
      'payload': payload
    };
    const response = UrlFetchApp.fetch(url, options);
    const content = response.getContentText();
    const result = content.match(/panel-default">([\s\S]+)<div class="footer/);
    if (!result) { return; }
    const regex = /form-group[\s\S]*?<(?:div|span).*?>([^<>]+)<\/(?:div|span)/g
    const matches = [...result[1].matchAll(regex)];
    if (matches.length === 0) { return; }
    const values = matches.map(match => match[1].trim());
    return values;
  }
}

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15328

May be improved ...

function senddatatoform() {
  var url = 'https://services.sia.homeoffice.gov.uk/PublicRegister/SearchPublicRegisterByLicence'; 
    var payload = {
    LicenseNo: "1012894854698842",
  }
  var options = {
    method: "POST",
    payload: payload
  }

  var response = UrlFetchApp.fetch(url,options);

  Logger.log(response.getContentText().replace(/(\r\n|\n|\r|\t|  )/gm,"").match(/<div class="panel-body">.*<div class="footer">/g)[0].replace(/(<([^>]+)>)/ig,"|").replace(/(\|)+/gm,"\n"))
}

Upvotes: 0

Related Questions