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.