Create daughter Google spreadsheet with edits using GAS

Question

I need help with a Google Apps Script that uses a template reporting file, and spawns individualized reports by setting an ID number into a referenced cell - which changes the dynamically updating report.

The script works properly, in that it creates a new file for each ID in the data array. However, all of the daughter files look identical to the parent (template). They do not reflect the changes to cell C3 based on the setValue(). However, after running the script, the template file does show that the setValue was making changes to C3 as the script ran.

Does parentFile.makeCopy() only copy the source spreadsheet as it was before the script ran, without edits? Do I need a different method?

Also, how would I go about changing the permissions of these daughter files? Would I have to change my active spreadsheet back and forth each time between new daughters and the source spreadsheet?

Thanks for any help!

I've included a copy of my script below:

function SpawnReports() {
  // gets the current Google Sheet file
  var parentFile = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // the spreadsheet must have a sheet named ReportList with 
  // Column A = IDs and Column B = emails without headings on columns
  var sheet = ss.getSheetByName("ReportList");
  var data = sheet.getDataRange().getValues();
  // determines timezone
  var timeZone = Session.getScriptTimeZone();
  // generates the timestamp and stores in variable 
  // formattedDate as year-month-date hour-minute-second
  var formattedDate = Utilities.formatDate(new Date(), timeZone , "yyyy-MM-dd' 'HH:mm:ss");
  // gets the destination folder using parent spreadsheet location (folder)
  var parentFolder = parentFile.getParents();
  var folder = parentFolder.next();
  var folderId = folder.getId();
  var destination = DriveApp.getFolderById(folderId);

  // loops through IDs
  for (var i = 0; i < data.length; i++) {
    Logger.log('ID: ' + data[i][0]);
    Logger.log('EMAIL: ' + data[i][1]);

    // Write ID to daughter spreadsheet reference cell
    sheet.getRange(1, 3).setValue(data[i][0]);

    // gets the name of the original file and appends the ID and 
    // the timestamp stored in formattedDate
    var name = SpreadsheetApp.getActiveSpreadsheet().getName() + " - " + data[i][0] + " Report " + formattedDate;

    // makes copy of "parentFile" with "name" at the "destination"
    parentFile.makeCopy(name, destination);
  }
}

Possible duplicate of [Creating and Sending sheets in the same function](https://stackoverflow.com/questions/48304224/creating-and-sending-sheets-in-the-same-function) — tehhowch, Jul 18 '18 at 21:47
You need to ensure the writes (and any related formula recalculations) are performed before you copy the templates. See also https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush — tehhowch, Jul 18 '18 at 21:48

score 1 · Answer 1 · edited Jul 18 '18 at 22:32

1

Does parentFile.makeCopy() only copy the source spreadsheet as it was before the script ran, without edits?

Yes. Apps Script could wait to apply the changes after the script has finished. If you need to be certain that the changes were applied before the copy is made, use SpreadsheetApp.flush() prior to making the copy.

Do I need a different method?

Maybe, if SpreadsheetApp.flush() is not enough. An alternative could be to log the child spreadsheets ids / URLs somewhere, and use the SpreadsheetApp methods open(File), openById(fileId) or openByUrl(fileUrl) to open the child spreadsheet. At that point, you could then use Range#setValue() to update the corresponding cell on the child spreadsheet.

It's worth noting that file.makeCopy() returns a File object referring to the new file. To get its ID and the open the child spreadsheet, you could use something like the following:

var child = SpreadsheetApp.open(parentFile.makeCopy("new name","parent Folder"));
saveFileIdSomehow(child.getId());
// do something with the child spreadsheet

edited Jul 18 '18 at 22:32

tehhowch

9,645
4
24
42

answered Jul 18 '18 at 21:43

Rubén

34,714
9
70
166

@tehhowch Rather that your comment I read your mind :) (I was editing my answer when you posted the comment) – Rubén Jul 18 '18 at 21:49
I'm fairly certain the changes are applied "as soon as is reasonable". The problem with a template instantiation is that the OP probably has a fair number of formulas that operate based on the given changed cell, and Google can detect this. For high-latency/response time operations, I feel that is where the most batch-write optimization occurs. – tehhowch Jul 18 '18 at 21:50
@tehhowch AFAIK if SpreadsheetApp.flush() isn't used we could not be certain that the changes were applied before the script finish. – Rubén Jul 18 '18 at 21:56
@Rubén I really like your idea, but could you help me understand how I might set the child spreadsheet id to a variable, in order to use SpreadsheetApp.openById(id) to open the child spreadsheet as you suggested? The makeCopy() method doesn't return an ID. Thanks so much for any help! – Joshua Jul 18 '18 at 22:05
1

@Joshua note that you can simply call [`SpreadsheetApp.open`](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openfile) if you have a `DriveApp#File` that you know is of `MimeType.GOOGLE_SHEETS` – tehhowch Jul 18 '18 at 22:21

Create daughter Google spreadsheet with edits using GAS

1 Answers1