I'm a noob, so "my" code is probably not what it should be. I used this (https://blog.gsmart.in/google-apps-script-send-email-with-attachment/) as inspiration an modified it a bit.
Let me first explain what I want to achieve. I have a lot of contact info of various people, and I want them to be able to check whether that info is still accurate. I've found a solution, in which I first extract "everyone" to a separate Google Sheet (following these guidelines: https://www.youtube.com/watch?v=eVGENHcrOc4). I then send everyone an email with only their info attached, with the above mentioned code, which I altered a bit.
It works (I run function "sendFormToAll()").
BUT. The automatically created Sheets have columns that are too narrow: the information isn't readable. So I fiddled around some more, and added "autoResizeColumns" in there somewhere. It took me some time to get it right, but now it works.
BUT still... When running "sendFormToAll()", the autoResizeColumns function doesn't get things done before the sendEmailWithAttachment function kicks in, so the pdf file that's sent out is still no good. If I later run the code again, the pdf file is good, since by then, all sheets have been resized.
So I'm looking for a way to run "sendFormToAll()", in which the system first lets autoResizeColumns do his thing, and only then start with sendEmailWithAttachment. I tried reading up on async/await, but I can't figure it out... Same deal with Promise and .then ... I tried to implement it, but with no effect.
Thanks for your help!
This is the code I've got now (including the useless async/await implementation):
function sendEmailWithAttachment(row)
{
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var values = sheet.getRange(row,1,row,4).getValues();
var rec = values[0];
var filename = rec[0]
var file = DriveApp.getFilesByName(filename);
if (!file.hasNext())
{
console.error("Could not open file "+filename);
return;
}
var client = getClientInfo(row);
var template = HtmlService
.createTemplateFromFile('email-template');
template.client = client;
var message = template.evaluate().getContent();
MailApp.sendEmail({
to: client.email,
subject: "The form to fill",
htmlBody: message,
attachments: [file.next().getAs(MimeType.PDF)]
});
}
function getClientInfo(row)
{
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var values = sheet.getRange(row,1,row,5).getValues();
var rec = values[0];
var client =
{
first_name: rec[1],
last_name: rec[2],
email: rec[4]
};
client.name = client.first_name+' '+client.last_name;
return client;
}
function correctColumns(row)
{
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var values = sheet.getRange(row,1,row,4).getValues();
var rec = values[0];
var filename = rec[0]
var file = DriveApp.getFilesByName(filename);
if (!file.hasNext())
{
console.error("Could not open file "+filename);
return;
}
while (file.hasNext()) {
var spreadsheet = SpreadsheetApp.open(file.next());
var sheet = spreadsheet.getSheets()[0];
sheet.autoResizeColumns(1, 15);
}
}
async function sendFormToAll()
{
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var last_row = sheet.getDataRange().getLastRow();
var last_column = sheet.getLastColumn()
for(var row=2; row <= last_row; row++)
{
await correctColumns(row);
sendEmailWithAttachment(row);
sheet.getRange(row,last_column).setValue("email sent");
}
}