NSGI Coordinate Transformation API Office Script - for transforming coordinates in MS Excel

README.md

NSGI Coordinate Transformation API in Excel

Zie MS Excel bestand: office-script-ct-api.xlsx voor hoe het script te gebruiken (script is al geincludeerd in xlsx bestand).

Om te gebruiken open het transformeer tabblad en klik op de transformeer knop.

nsgi-coordinate-transformation-api-client.ts

// consts
const DEFAULT_API_KEY = "l75526b51269c9481081b2be9122d03c4f"; // demo api-key also available in Open API Spec of https://api.transformation.nsgi.nl/v2/openapi
const TARGET_SHEET_POSTFIX = "-transformed"; // appended to sourceSheetName and new sheet created for transformed coords
const REQUIRED_CONFIG_KEYS = [
  "x-column",
  "y-column",
  "source-crs",
  "target-crs",
  "api-key",
  "source-sheet",
  "target-axis-labels",
]; // note z-column is not required when transforming 2D
const CONTSTANTS_TABLE_NAME = "coordinate_transformation_config";
const API_BASE_URL = "https://api.transformation.nsgi.nl/v2";

class ApiError extends Error { }
// types
type PointGeometry = {
  type: String;
  coordinates: number[];
};

type PointFeature = {
  type: string;
  geometry: PointGeometry;
  properties: {};
};

type FeatureCollection = {
  type: string;
  features: PointFeature[];
};

// generic helper functions
if (!Object.fromEntries) {
  Object.fromEntries = function (entries) {
    if (!entries || !entries[Symbol.iterator]) {
      throw new Error(
        "Object.fromEntries() requires a single iterable argument",
      );
    }
    let obj = {};
    for (let [key, value] of entries) {
      obj[key] = value;
    }
    return obj;
  };
}

function sortKeysByValue(dict: { [key: string]: number }): string[] {
  // Convert the object into an array of key-value pairs
  let entries = Object.entries(dict);
  entries = entries.filter((x) => x[1]);
  // Sort the entries based on the numerical value
  entries.sort((a, b) => a[1] - b[1]);
  // Extract and return the sorted keys
  return entries.map((entry) => entry[0]);
}

function splitArray(
  array: (string | number)[][],
  size: number,
): (string | number)[][][] {
  return Array.from(new Array(Math.ceil(array.length / size)), (_, i) =>
    array.slice(i * size, i * size + size),
  );
}

function flattenArray(arr: unknown[][]) {
  return arr.reduce((accumulator, value) => accumulator.concat(value), []);
}

// helper functions
function getConstants(workbook: ExcelScript.Workbook) {
  let table = workbook.getTable(CONTSTANTS_TABLE_NAME);
  if (!table) {
    throw new Error(
      `Table '${CONTSTANTS_TABLE_NAME}' missing. Table must contain the following config options (as key value pairs in columns A and B, with headers: key, value): ${REQUIRED_CONFIG_KEYS.join(", ")}.`,
    );
  }
  let values = table.getRange().getValues();
  let result = {};
  for (let i = 0; i < values.length; i++) {
    let key = values[i][0].toString();
    let value = values[i][1];
    if (!key || !value) {
      // filter out empty values
      continue;
    }
    result[key] = value;
  }
  let missingKeys = REQUIRED_CONFIG_KEYS.filter(
    (k) => !Object.keys(result).includes(k),
  );
  if (missingKeys.length > 0) {
    throw new Error(
      `Missing config options in table '${CONTSTANTS_TABLE_NAME}': ${missingKeys.join(", ")}.`,
    );
  }
  // check if source sheet exists
  let sourceSheet = workbook.getWorksheet(result["source-sheet"]);
  if (!sourceSheet) {
    throw new Error(
      `Worksheet source-sheet: '${result["source-sheet"]}' missing. Check '${CONTSTANTS_TABLE_NAME}' table.`,
    );
  }
  return result;
}

function getTargetWorksheetName(sourceSheetName: string): string {
  const sheetNameMaxLength = 31; // sheet name can only be 31 chars long
  let trimLength = sheetNameMaxLength - TARGET_SHEET_POSTFIX.length;
  return `${sourceSheetName.substring(0, trimLength)}${TARGET_SHEET_POSTFIX}`.substring(
    0,
    31,
  ); // substring in case postfix.length>31
}

function getTargetSheet(
  workbook: ExcelScript.Workbook,
  sourceSheetName: string,
): ExcelScript.Worksheet {
  let targetSheetName = getTargetWorksheetName(sourceSheetName);
  let targetSheet: ExcelScript.Worksheet =
    workbook.getWorksheet(targetSheetName);
  if (targetSheet) {
    // delete targetsheet if exists and recreate to ensure no old data is left behind
    targetSheet.delete();
  }
  workbook.addWorksheet(targetSheetName);
  targetSheet = workbook.getWorksheet(targetSheetName);
  return targetSheet;
}

function defaultApiKeyCheck(
  values: (string | number | boolean)[][],
  apiKey: string,
) {
  // do not transform more than 1000 points if api-key equals default api-key
  if (values.length > 20000 && apiKey === DEFAULT_API_KEY) {
    new Error(
      `ERROR: transforming more than 1000 points with the default API key is discouraged. The default API key is only for evaluation purposes only. Pleas1e request a API key through https://formulieren.kadaster.nl/aanvragen-coordinatentransformatie-api`,
    );
  }
}

function getFeatureCollection(points: (string | number)[][]) {
  let features: string[] = points
    .filter((x) => x[0] !== "" || x[1] !== "")
    .map((point) => {
      // convert number values to string, with "." as decimal seperator
      // see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Number/toString
      let coordinates: string[] = point.map((y) => {
        if (typeof y === "number") {
          return y.toString();
        } else {
          return y;
        }
      });
      return `{
        "type": "Feature",
        "properties": {},
        "geometry": {
            "type": "Point",
            "coordinates":
                [
            ${coordinates.join(",")}
            ]
        }
    }`;
    });
  let fcString = `{
        "type": "FeatureCollection",
        "features": [ ${features.join(",")}]
    }`;
  return JSON.stringify(JSON.parse(fcString)); // minifies json string
}

async function transformFeatureCollection(
  fc: string,
  constants: {},
  requestNr: number,
  requestTotal: number,
): Promise<FeatureCollection> {
  const apiKey: string = constants["api-key"];
  let url = `${API_BASE_URL}/transform?source-crs=${encodeURIComponent(constants["source-crs"])}&target-crs=${encodeURIComponent(constants["target-crs"])}`;
  let headers = new Headers();
  headers.append("apikey", apiKey);
  headers.append("User-Agent", "office-script-ct-api-client");
  headers.append("Content-Type", "application/json");
  headers.append("Accept", "application/json");
  return fetch(url, {
    method: "POST",
    headers: headers,
    body: fc,
  }).then((response) => {
    if (response.ok) {
      console.log(
        `INFO: received HTTP OK for request ${requestNr + 1}/${requestTotal}`,
      );
      return response.json();
    } else {
      if (response.headers.get("content-type") == "application/problem+json") {
        response.json().then((json) => {
          console.log(
            `ERROR: HTTP Status ${json.status} -  ${json.title} - ${json.detail}: \n${JSON.stringify(json.errors, null, 2)}`,
          );
        });
      } else {
        response.text().then((text) => {
          console.log(`ERROR: HTTP Status ${response.status}: \n${text}`);
        });
      }
    }
  });
}

function checkCoordinateCellValues(
  coordinateCellValues: (string | number | boolean)[][],
): (string | number)[][] {
  // last row(s) can contain empty values, drop empty last rows until we find actual values
  // so loop through values backward and drop values until we find non-empty val
  for (let i = coordinateCellValues.length - 1; i >= 0; i--) {
    if (coordinateCellValues[i].every((x) => x === "")) {
      // check if all values empty
      coordinateCellValues.pop();
    } else {
      break;
    }
  }
  coordinateCellValues.forEach((coordinateCellValue) => {
    coordinateCellValue.forEach((coordinate) => {
      if (!["number", "string"].includes(typeof coordinate)) {
        throw new Error(
          `Encountered coordinate value: ${coordinate}, only string or number values allowed for coordinate values.`,
        );
      }
      if (typeof coordinate === "string" && isNaN(parseFloat(coordinate))) {
        throw new Error(
          `Encountered coordinate value: ${coordinate}, unable to parse this value as number.`,
        );
      }
    });
  });
  return coordinateCellValues as (string | number)[][];
}

function reorderColumnsCoordinates(
  coordinates: (string | number)[][],
  coordinatesColIndex: string[],
): (string | number)[][] | null {
  // create indexMap -> {x:0, y:1, z:2}
  const columnIndexMap: { string: number } = Object.fromEntries(
    coordinatesColIndex.map((x) => [x, coordinatesColIndex.indexOf(x)]),
  );
  // Get the new column order indices based on newColIndex
  const newColIndex =
    coordinatesColIndex.length === 2 ? ["x", "y"] : ["x", "y", "z"]; // order required per JSON spec
  const newOrderIndices: number[] = newColIndex.map(
    (col) => columnIndexMap[col],
  );
  return coordinates.map((x) => newOrderIndices.map((index) => x[index]));
}

function readCoordinatesInBatch(
  sourceSheet: ExcelScript.Worksheet,
  constants: {},
  batchSize = 1000,
): (string | number)[][][] {
  let x: number = constants["x-column"];
  let y: number = constants["y-column"];
  let z: number | null =
    constants["z-column"] === undefined ? null : constants["z-column"];

  const columnLookup = {
    x: x,
    y: y,
    z: z,
  };
  let twoDim = (!z);
  let minCol = twoDim ? Math.min(x, y) : Math.min(x, y, z);
  let maxCol = twoDim ? Math.max(x, y) : Math.max(x, y, z);

  let colDiff = twoDim? 1: 2;

  if (maxCol-minCol!==colDiff){
    let message = "Columns x-columns, y-column"
    if (! twoDim){
      message+= ", z-column"
    }
    throw new Error(`${message} are not contiguous, coordinate columns should be adjacent to eachother.`)
  }

  const colOrder: string[] = sortKeysByValue(columnLookup);
  let rowCount = sourceSheet.getUsedRange(true).getRowCount();
  // Get the range containing the data
  // startRow, startColumn, rowCount, columnCount
  let range = sourceSheet.getRangeByIndexes(
    1,
    minCol - 1,
    rowCount,
    colOrder.length,
  );
  let coordinateCellValues: (string | number | boolean)[][] = range.getValues();

  defaultApiKeyCheck(coordinateCellValues, constants["api-key"]); // throws error if not OK
  let coordinateCellValuesChecked: (string | number)[][] =
    checkCoordinateCellValues(coordinateCellValues);

  let coordinateCellValuesXYZ = reorderColumnsCoordinates(
    coordinateCellValuesChecked,
    colOrder,
  );
  // batch transform coordinates in batches of 1000
  let splitCoordinates: (string | number)[][][] = splitArray(
    coordinateCellValuesXYZ,
    batchSize,
  );
  return splitCoordinates;
}

// main function
async function main(workbook: ExcelScript.Workbook) {
  const constants = getConstants(workbook);
  let sourceSheetName: string = constants["source-sheet"];
  let sourceSheet = workbook.getWorksheet(sourceSheetName);

  let coordinates = readCoordinatesInBatch(sourceSheet, constants);
  let fcs = coordinates.map((x) => getFeatureCollection(x));
  let count = 0;
  let promises: Promise<FeatureCollection>[] = fcs.map((fc) => {
    let promise = transformFeatureCollection(
      fc,
      constants,
      count,
      coordinates.length,
    );
    count++;
    return promise;
  });

  let targetSheet = getTargetSheet(workbook, sourceSheetName);
  let first = true;
  await Promise.all(promises).then((fcs) => {
    // Promise.all return fulfillment values as an array of fulfillment values, in the order of the promises passed, regardless of completion order
    if (fcs.some((fc) => fc === undefined)) {
      // in case any fc turned up undefined error occured in request, abort script
      throw new Error(
        `An error occured in one or more HTTP requests to the NSGI Coordinate Transformation API (${API_BASE_URL}/openapi), script aborted.`,
      );
    }
    let allFeatures: PointFeature[] = flattenArray(
      fcs.map((fc) => [...fc.features]),
    ) as PointFeature[]; // merge featurePoints back in one list
    for (let i = 0; i < allFeatures.length; i++) {
      let ft = allFeatures[i];

      // returned feature from API can have missing geometry when transformation results in NaN values
      let coords: (number)[] = ft.geometry?.coordinates ?? [NaN, NaN, NaN];
      
      let axisLabels: string[] = constants["target-axis-labels"].split(",");
      for (let j = 0; j < coords.length; j++) {
        if (first) {
          targetSheet.getCell(0, j).setValue(axisLabels[j]);
        }
        targetSheet.getCell(i + 1, j).setValue(JSON.stringify(coords[j]));
      }
      first = false;
    }
    let coordinateCount = coordinates
      .map((x) => x.length)
      .reduce((partialSum, x) => partialSum + x, 0);
    console.log(
      `INFO: coordinates (${coordinateCount}) transformed from ${constants["source-crs"]} to ${constants["target-crs"]}. Results saved in worksheet '${targetSheet.getName()}'.`,
    );
  });
}