A question in the Apps Script community prompted me to write this post about file conversion and the nature of files on Drive. If you use the Google Drive client on Windows or Mac, and take a look at what gets synced – it’s not the data in your document as you might expect, but rather its a collection of text links, saying where the data is.

That means that the concept of converting a file from one format to another is a bit different than it would be if you were dealing with a file that was full of data – as opposed to links to data.

This file meta data can be retrieved using the Advanced Drive service in the form of a File Resource Representation.

There is an exportLinks property, which is an object with properties keyed by MimeTypes, and with values of URLs which lead to a queries that can be used to retrieve the file contents in various formats.

exportLinks object Links for exporting Google Docs to specific formats.

For a spreadsheet, these exportLinks look like this.

{

 "application/x-vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=1aHupofMdrR_vWG27qReKs-tTNRDpL6r8jl53rce1XUo&exportFormat=ods",

 "application/pdf": "https://docs.google.com/spreadsheets/export?id=1aHupofMdrR_vWG27qReKs-tTNRDpL6r8jl53rce1XUo&exportFormat=pdf",

 "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "https://docs.google.com/spreadsheets/export?id=1aHupofMdrR_vWG27qReKs-tTNRDpL6r8jl53rce1XUo&exportFormat=xlsx",

 "text/csv": "https://docs.google.com/spreadsheets/export?id=1aHupofMdrR_vWG27qReKs-tTNRDpL6r8jl53rce1XUo&exportFormat=csv",

 "application/zip": "https://docs.google.com/spreadsheets/export?id=1aHupofMdrR_vWG27qReKs-tTNRDpL6r8jl53rce1XUo&exportFormat=zip"

}

So this tells me that, simply by retrieving the blob at each of those Urls, I can convert a sheet to ods, pdf, xlsx, csv, and zip. You can get more information on each of these mimetypes here. No coding or conversion needed – just fetch the blob at the Url.

The solution

So to get back to the community question, how to schedule something that converts a sheet to an excel file every now and again, the solution is simple. It needs a little sugar, since the op wants to use folder and file names rather than file IDS, but that’s simple enough.

It needs to use Advanced Drive to be able to get access to these Urls – which means you’ll need to authorize the Advanced Drive service in Apps Script and in the developer console.

I’ll just use the regular built in DriveApp to play around with folder and file names as it’s a little easier that way.

The code

Here’s the complete solution.

function scheduleThis () {
  
  var INPUT_FILE_PATH = '/Published Sheets/gasParse';
  var OUTPUT_FOLDER_PATH = '/etest/';
  var OUTPUT_FILE_NAME = 'output.xlsx';
  
  // get the input sheet
  var files = Worker.getFilesFromPath (INPUT_FILE_PATH);
  
  // check we only have one
  for (var matches = [] ; files && files.hasNext() ;) {
    matches.push(files.next() )
  }
  if (matches.length !== 1 ) {
    throw 'expected to find exactly 1 ' + INPUT_FILE_PATH +
      ' but found ' + matches.length;
  }
  // get the content
  var blob = Worker.makeExcel (matches[0].getId()).setName (OUTPUT_FILE_NAME);
  
  // delete any output file
  var folder = Worker.getFolderFromPath (OUTPUT_FOLDER_PATH);
  if (!folder) throw 'couldnt find folder ' + OUTPUT_FOLDER_PATH;

  var files = folder.getFilesByName(OUTPUT_FILE_NAME);
  while (files.hasNext() ) {
    files.next().setTrashed(true);
  }
  
  // now write the new file
 
  Drive.Files.insert({
    title:blob.getName(),
    parents:[{id:folder.getId()}]
  }, blob);


}

var Worker = (function (ns)  {
  
  /**
  * get the files from a path like /abc/def/hgh/filename
  * @param {string} path the path
  * @return {FileIterator} a fileiterator
  */
  ns.getFilesFromPath = function (path) {
    
    // get the filename and the path seperately
    var s = path.split("/");
    if (!s.length) { 
      return null;
    }
    var filename = s[s.length-1];
    
    // the folder
    var folder = ns.getFolderFromPath (
      "/" + (s.length > 1 ? s.slice(0,s.length-1).join("/") : "")
    );
    
    return folder ? folder.getFilesByName (filename) : null ;
    
  }
  
  
  /**
  * get a folder from a path like /abc/def/hgh
  * @param {string} path the path
  * @return {Folder} a folder
  */
  ns.getFolderFromPath = function (path) {
    
    return (path || "/").split("/").reduce ( 
      function(prev,current) {
        if (prev && current) {
          var fldrs = prev.getFoldersByName(current);
          return fldrs.hasNext() ? fldrs.next() : null;
        }
        else { 
          return current ? null : prev; 
        }
      },DriveApp.getRootFolder()); 
  };
  
  /**
   * need to use Advanced Drive service to convert format
   * make excel
   * @param {string} id the file id
   * @return {blob} the file as excel blob
   */
  ns.makeExcel = function (id) {
    
    
    // get the file meta data
    var meta = Drive.Files.get (id);
    if (!meta) throw 'Couldnt get file meta data by id';

    // the conversion is called by using a Drive export
    // the mimes are here
    // http://filext.com/faq/office_mime_types.php
    // allow it to throw exception if fails
    return UrlFetchApp.fetch ( 
      meta.exportLinks['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'], {
      headers: {
        Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
      }
    }).getBlob();
    
  };
  
  return ns;
  
  } ) (Worker || {} );;

Note…..

If you get an error about Drive being undefined, it means you still haven’t done this: ‘you’ll need to authorize the Advanced Drive service in Apps Script and in the developer console’, so go and do that now. It’s in the resource menu.

For more like this see Google Apps Scripts Snippets
Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.