api - 尝试使用应用脚本从该站点获取数据到谷歌表格

Question

任何人都可以帮助我转换来自该站点的响应的代码 https://services.sia.homeoffice.gov.uk/PublicRegister/

我需要它从谷歌表中读取许可证号 - 例如：1012894854698842

并将结果填充到同一个电子表格中

我一直在玩邮递员和网站抓取工具，但仍然无法弄清楚

请帮忙！！谢谢你

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

我需要的信息是

名

姓

牌照号码

角色

许可部门

到期日

地位

状态说明

附加许可条件

许可证号在每个员工的电子表格中

标签： apigoogle-sheets

Answer 1

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;
  }
}