xmlgoogle-apps-scriptgoogle-sheetskmz

Write data from kmz on sheet with Google Apps Script


I'm very new in Google Apps Script. I have to read data from multiple kmz (placemarks and desccriptions mainly). So far I have managed to read and parse it with XmlService. What is the best way to transfer data to the spreadsheet?

  var kmzFile = DriveApp.getFileById('<ID>').getBlob();
  var unzipBlobs = Utilities.unzip (kmzFile.setContentType('application/zip'));
  var xml = unzipBlobs[0].getDataAsString()
  var document = XmlService.parse(xml)
  var root = document.getRootElement();
  var nameSpace = root.getNamespace()
  var folderRoot = root.getChild('Document', nameSpace).getChild('Folder', nameSpace)
  var folders = folderRoot.getChildren('Folder', nameSpace)

Solution

  • How to parse the kmz depends on the structure of the specific document, but assuming your data of interest is contained in folders:

    Here is a sample of how to insert the retrieved kmz data into a new sheet of a given spreadsheet:

    var ss = SpreadsheetApp.openById("PASTE THE SPREADSHEET ID HERE");
    var newSheet = ss.insertSheet();
    var values = [];
    if(folders.length!=0){
      for (var i = 0; i< folders.length; i++){
          values[i]=[];
          values[i].push(folders[i]);
        }
      newSheet.getRange(1,1,values.length, values[0].length).setValues(values);
      }
    

    Basically, you iterate through all folders and dynamically create a value range of the size of the data and then assign it to a range in a sheet.