Identifying hosted files

In Finding where Drive hosting is being used in Sites, we created a couple of sheets by examining the hosted files referenced in a Google Site so that we could sort out the world after Google Hosting goes away. The next step is to identify those files and organize them. This process is driven from the sheets created by Finding where Drive hosting is being used in Sites, but they could be created by any process if you have to do this for a platform other than Sites. 

The report

You get 1 report, which summarizes all the files that need attention into one row per file, with a reorganization of the layout of the hosted files so they can easily be loaded to the new hosting place - whichever method you choose. At this stage, it's independent of that. The filePath below is how the files will be re-organized. No files are copied yet - that'll happen in the next phase, but this is how it proposes to re-organize the googledrive hosted files. Note that it works with both and variations.

This report is used as input to the next phase, Copying to new host location

cUseful library

I'll be using the cUseful library, specifically this techniques.
Here's the key for the cUseful library, and it's also on github, or below.



It starts with the settings, which have been expanded to now look like this, and we're mainly concerned with the Settings.identify section.
var Settings = (function(ns) {

  ns.sites = {
    search:"",                                 // enter a search term to concentrate on particular pages
    siteName:"share",                          // site name
    domain:"",                       // domain
    maxPages:0,                                // max pages - start low to test
    maxChunk:200                               // max children to read in one go
  }; = {
    sheetId:"19kXFMUh0DNTde_qtvsZ6FH4dAJ4YDCJhIJroLNT3vGY",         // sheet id to write to
    sheetName:'site-' + ns.sites.domain + '-' + ns.sites.siteName,  // sheetName to write to
    sheetHosting:'hosting-' + ns.sites.domain + '-' + ns.sites.siteName

  ns.identify = {,                  // where to write the results
    sheetFiles:"identify-" +,
    treatments: {
      fileName:"{name}",                          // template for output file name - can be {id}-{name}
      path:"/hosting/{mime}/",                    // template for file path  can be  {id}-{name}-{drivePath}-{mime}
      failOnMissing:false,                        // whether to fail if file is missing

  return ns;
})(Settings || {});

Some notes on the settings

  • This will create a new sheet, so sheetId and sheetFiles say where.
  • treatments are how to handle situations and to organize the files
    • filename is a template to construct the new file name- It can be any text you like, and you can also include the {id} and {name} somewhere in the new file name, where id and name are the  values for the current file.
    • path is an output path describing the folder structure the new files will end up. Here you can use any text plus {id},{name},{drivePath} and {mime} where id and name refer to the  name and id of the current file, drivePath is the path of its current folder and mime is an abbreviated form of its mime type.
    • failOnMissing - if a file is missing and this is true it will stop processing
    • missingText - is the text to use for a filename if it the file is missing

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.

The code

It's on GitHub, or below, or copy of developing version here. You'll need the settings namespace at the beginning of this post too and of course the cUseful library reference. 
 * copy the matched files from 
 * sheet created in look for hosting
 * make a new sheet with their new names
function identifyHostingFiles () {
  // get the data
  var sr =;
  var si = Settings.identify;
  var du = cUseful.DriveUtils.setService(DriveApp);
  var fiddler = new cUseful.Fiddler();
  fiddler.setValues (
  // map the file names
  var work = fiddler.getData().reduce(function (p,c) {
    // this may be a folder/filename kind of deal
    if (!p.files.hasOwnProperty( + c.fileName)) {
      // now find the file
      var file = du.getFileById (;
      var fob = p.files[] = {,
      fob.referenceCount = 0;
      if (file) {
        // if its the folder/name model, then we're not yet done
        if (c.fileName && du.getShortMime(file.getMimeType()) !== "folder") {
          // this is going to be a missing file
          file = null;
        else if (du.getShortMime(file.getMimeType()) === "folder") {
          // in this cae the id refers to the parent.
          var folder = DriveApp.getFolderById(file.getId());
          file = folder.getFilesByName(c.fileName);
          if (!file.hasNext()) {
            file = null;
          else {
            file =;
      if (file) {
        fob.originId = file.getId(); = file.getName();
        fob.path = du.getPathFromFolder(file.getParents().next());
        // set up the new names and paths
        fob.fileName = si.treatments.fileName
        fob.folderPath = si.treatments.path
        fob.filePath = fob.folderPath + fob.fileName;
        // what to do if we already have this file path?
        if (p.paths.hasOwnProperty(fob.filePath)) {
          if (p.paths[fob.filePath].id !== file.getId()) {
            throw new Error('Would create ambigous filepath for ' + file.getId() + ' ' + fob.filePath);
        else {
          p.paths[fob.filePath] = file.getId();
      else {
        if (si.treatments.failOnMissing) {
          throw new Error ('cant find file id ' + + " " + c.fileName);
        } = fob.path = fob.filePath = fob.fileName = fob.folderPath = si.treatments.missingText;


    return p;
  },{files:{}, paths:{}});
  // copy them to new location
  // write out the result to a new sheet
  var ss = SpreadsheetApp.openById(si.sheetId);
  var sh = ss.getSheetByName(si.sheetFiles);
  // if if does exist, create it.
  if (!sh) {
    sh = ss.insertSheet(si.sheetFiles);
  // clear it
  var fileFiddler = new cUseful.Fiddler();
  fileFiddler.setData(Object.keys(work.files).map(function(k) {
    return work.files[k];
  .filterColumns(function (name) {
    // get rid of stuff not required
    return name !== "url" && name !== "index";

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