[SOLVED] Parsing GEOJSON and writing to sheet

[Dirk]

I have an GEOJSON-export of umap an uploaded it here for testing purposes:

https://inliner-rheinmain.de/wp-content/uploads/geojson/Test.geojson

Now I want to parse it an put it in a sheet, that it looks like this (is done with a webtool).

I've started with coding....here. Parsing the coordinates will lead to "Ljava.lang.Object" instead of the content.

Does anyone know, how to get this done?

[Michael Ellis]

Dirk,

Bruce McPherson has written a JSON parsing tool to take deeply nested JSON results and place them in a spreadsheet.   I have not used it yet because I didn't have a use case.  Here is a link to the discussion of it.   Hopefully this helps - I'd be interested to see your results.

https://groups.google.com/forum/#!searchin/google-apps-script-community/unnest$20json%7Csort:date/google-apps-script-community/A0o-5A9TbCc/T-ehlEWWAwAJ

Mike

[Dirk]

Thank you, Mike! Meanwhile I have found the solution myself. It is not that elegant but enough for my purpose.

You can see it here live: https://docs.google.com/spreadsheets/d/1rTG_A9tYQaEcjPD0SK1z26CQZ7ZHToAnMvpi_L4DSCM/edit#gid=0

And the script...

https://script.google.com/d/MJyyMtb_kyIs-rhX11uVD8ykiw-oP-QUs/edit?mid=ACjPJvHW0eGfCQLjsrEv8WdQyB2zPblafG1b96y0y20dtEgICW8MW3YfX1TXN959rz4uDCExD-5AJD_PCrNMnfdqIvVBEjRWrKy4ZtihlAmCqZNjOLdbC3daL0ar8S3x4iqjWc_mLFk3nZY&uiv=2

EDIT 18:50 hrs / 04.01.2020 - I have added the following code

I do not why, but in some cases the array has to be addressed with an additional dimension at the end [0]! This is really really strange! If someone can explain that...I am interested!

    //I have added this....otherwise it is not working reliable! BUT: I do not know WHY!!!
if (lengthc < 2) {
    coordinatesfull = feature[i]["geometry"]["coordinates"][0];
    lengthc = coordinatesfull.length;
    }

function getgeojson() {
 
   
  //Delete old data
  var activesheet = SpreadsheetApp.getActiveSpreadsheet();
  var lastrow = activesheet.getRange("E:E").getValues();
  lastrow = lastrow.filter(String).length + 1;
  activesheet.getRange('E2:H' + lastrow).clearContent();


  //Get the GEOJSON-Data
  var url="https://myhomepage.xyz/Test.geojson"; // Paste your JSON URL here
 
  //Parse the Geojson Data
  var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText());
  var feature = dataAll.features;
  var coordinates = [];
 
  //Get the amount of data (in this case ROUTES)
  var anzahl = feature.length;
 
  //Aray Features
  feature.push([dataAll.features]);
 
  //Write the data to the sheet
  for (var i=0; i<anzahl; i++){ //Gehe alle Datensätze durch (Strecken)
  coordinates[i] = '';  
  var coordinatesfull = feature[i]["geometry"]["coordinates"];
  var lengthc = coordinatesfull.length;
   
    //In some cases the array has to be extended with [0] at the end. Otherwise not all data will be fetched! Strange!
    if (lengthc < 2) {
    coordinatesfull = feature[i]["geometry"]["coordinates"][0];
    lengthc = coordinatesfull.length;}
   
  for (var t=0; t<lengthc-1; t++){ //Collect the coordinates
  coordinates[i] += coordinatesfull[t][0] + "," + coordinatesfull[t][1] + ",";
  }
  coordinates[i] += coordinatesfull[lengthc-1][0] + "," + coordinatesfull[lengthc-1][1];
 
  activesheet.getRange('E'+(i+2)).setValue(coordinates[i]); //Write the coordintes
  activesheet.getRange('F'+(i+2)).setValue(feature[i]["properties"]["name"]); //Write the name
  activesheet.getRange('H'+(i+2)).setValue(feature[i]["properties"]["ID"]); //Write the ID
 
  //If "description" does not have data, delete it, otherwise "undefined" will appear
  if (feature[i]["properties"]["description"] == null) activesheet.getRange('G'+(i+2)).setValue("");
  else activesheet.getRange('G'+(i+2)).setValue(feature[i]["properties"]["description"]);
  }}