const keyoptions = {
  method: "GET",
  headers: {
    "Accept": "application/json+v6",
    "x-api-key": "XXXXXXXXXXXXXXXXXXXXXXXXX"
  }
};

function getVRMData(row, column) {
  const vrm = SpreadsheetApp.getActiveSheet().getRange(row, column).getValue();
  const url = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' + vrm;
  const response = UrlFetchApp.fetch(url, keyoptions);
  const data = JSON.parse(response.getContentText())[0].motTests;
  // extract info from reply

  const rows = data.map(function(d) { return d.expiryDate; });
  const resultRows = data.map(function(d) { return d.testResult; });
  const rfrAndCommentsRows = data.map(function(d) { return d.rfrAndComments; });
  return {
    "rows": rows,
    "resultRows": resultRows,
    "rfrAndCommentsRows": rfrAndCommentsRows
  };
}

function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const destSheet = ss.getSheetByName("customfunction");
  destSheet.getRange('A4:F').clearContent();
  const lastRow = destSheet.getLastRow();

  for (var row = 2; row <= 10; row++) {
    var data = getVRMData(row, 1);
    const rows = data['rows'];
    const resultRows = data['resultRows'];
    const rfrAndCommentsRows = data['rfrAndCommentsRows'];
    sheet.getRange(lastRow + row, 1, rows.length, 1).setValues(rows);
    sheet.getRange(lastRow + row, 2, resultRows.length, 1).setValues(resultRows);
    sheet.getRange(lastRow + row, 3, rfrAndCommentsRows.length, 1).setValues(rfrAndCommentsRows);
  }
}