Is there a way to save a google spreadsheet as a blob in a database and then, during apps script, open that blob as a spreadsheet

Question

I have a very complex Google spreadsheet that I am using as a template to create another spreadsheet. The sheets have formatting, named ranges, data validation, formulas, data, permissions, etc. The code will select certain sheets within the template spreadsheet and copy them to the new spreadsheet. Example of code that does this:

var s = "complexity";
var srcsheet = ss_src.getSheetByName(s);
var newsheet = srcsheet.copyTo(ss).setName(s).hideSheet();
rebuildNamedRange(ss,newsheet);
var rule = ss_src.getSheetByName(s).getRange("C2").getDataValidation();
newsheet.getRange("C2:C99").clearDataValidations();
newsheet.getRange("C2:C99").setDataValidation(rule);
copySheetProtections(srcsheet,newsheet);

The issue is that the "copyTo" functions require that the user has edit privileges to the spreadsheet. Obviously that is not ideal.

Is there a way to save a google spreadsheet as a blob in a database and then, during apps script, open that blob as a spreadsheet? I've tried the following to no avail.

Test#1

function getBlob() {
  var doc = SpreadsheetApp.openById(SRC);
  var docContentBlob = doc.getBlob();
  copyBlobSheet(docContentBlob);
}
function copyBlobSheet(blob) {
  var dst = SpreadsheetApp.openById(DST);
  blob.setContentType('application/vnd.google-apps.spreadsheet'); // tried with and without this
  var newDocfromBlob = DriveApp.createFile(blob).setName("doc from blob");
  var src = SpreadsheetApp.open(newDocfromBlob); // this fails
  src.getSheetByName("one").copyTo(dst).setName("copy of one success");
}

Test#2

function getBlob() {
  var doc = SpreadsheetApp.openById(SRC);
  var docContentBlob = doc.getBlob();
  copyBlobSheet(docContentBlob);
}
function copyBlobSheet(blob) {
  var dst = SpreadsheetApp.openById(DST);
  blob.setContentType('application/vnd.google-apps.spreadsheet'); // tried with and without this
  var file = {
    title: 'advanced_test',
    mimeType: 'application/vnd.google-apps.spreadsheet'
  };
  newFile = Drive.Files.insert(file, blob); // this fails with "invalid mime type"
}

Any thoughts on this capability or a work-around would be greatly appreciated!

Jared Pinkham Jared Pinkham · Accepted Answer · 2016-05-13T02:17:47

A work around answer:

View access should be enough for them to copy the template. Once they have their own copy of the master template they run your script from inside it to make another sheet with the correct subset of properties.

Is there a way to save a google spreadsheet as a blob in a database and then, during apps script, open that blob as a spreadsheet

1 Answers