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

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