CODEWITHSUNDEEP
google-apps-scriptgoogle-drive-apigoogle-docs
New_2_Code
New_2_Code

Reputation: 328

Weird Behaviour with DriveApp.getFileById()

Code is still in Proof of Concept phase - Will try to explain the expected result and what result I am getting.

This script will be a generic mail merge. The script is expected to do the following:

While everything above works really well the weird behaviour comes when attaching the edited copy of the template. Instead of getting an edited version of the template I am getting an unedited version of the template attached to the email

function myFunction() {


var ui = SpreadsheetApp.getUi();
var response = ui.prompt('Please enter the ID of your template file.', ui.ButtonSet.OK_CANCEL);

if(response.getSelectedButton() == ui.Button.OK){
var templateid = response.getResponseText()
}//End of IF Statement

var docid = DriveApp.getFileById(templateid).makeCopy().getId();
var doc = DocumentApp.openById(docid);
var body = doc.getBody();

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastColumn = sheet.getLastColumn();

sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setNumberFormat("@");


var array = [];

for(var i = 1; i < lastColumn-1; i++) {
var headers = sheet.getRange(1, i, 1, 1).getValue();
array.push(headers)
}//END OF OUTER 1

for (var k = 2; k < sheet.getLastRow()+1; k++){
var row = sheet.getRange(k, 1, 1, lastColumn).getValues()

for (var j = 0; j <array.length; j++){
body.replaceText("<"+array[j]+">", row[0][j]);
}//END OF INNER


var recipient = sheet.getRange(k, lastColumn - 1, 1, 1).getValue();
var emailBody = "Good Day,\n\nPlease see attatched.\n\nRegards\nPerson's Name Here"
var subject = "See attatched";
var attatchment = DriveApp.getFileById(doc.getId());

Logger.log(doc.getId())


MailApp.sendEmail(recipient, subject, emailBody, {attachments: attatchment})

}//END OF OUTER 2
}//END OF FUNCTION

Spreadsheet Used:

enter image description here

Expected Result:

enter image description here

Actual Result:

enter image description here

Upvotes: 1

Views: 574

Answers (1)

Tanaike
Tanaike

Reputation: 201338

From your script, I thought that it might be required to use saveAndClose(). So in your script, for example, please add it before var attatchment = DriveApp.getFileById(doc.getId()); as follows.

var subject = "See attatched";
doc.saveAndClose(); // Added
var attatchment = DriveApp.getFileById(doc.getId());

Reference:

Additional information:

At the classes of DocumentApp, SpreadsheetApp and SlidesApp, when the Doc is modified by the methods of each class, in the case that the script is running, the modifications are reflected by saving the Doc using the save method. On the other hand, at Google APIs of Sheets API and Slides API, when each method is called and run, the modifications are reflected, even if the script is running. At Google Document, there is no Docs API yet. But recently, the form about the early access of Docs API is released. When Docs API got to be able to be used, I think that the methods of API might be able to edit and save the document without using saveAndClose().

Upvotes: 3

Related Questions