脚本过早清除数据，有谁知道为什么? [英] Script is clearing data too early, does anyone know why?
问题描述
所以我正在使用脚本在 Google 表格中开发一个项目，最终将执行以下操作；
So I'm working on a project in Google Sheets, using scripting, that will eventually do the following;
首先，根据 Cell 中的姓名，在表单回复中找到该人的最后 9 个条目.
Firstly, based on a name in a Cell , find the last 9 entries for that person in form responses.
然后它以我需要的方式排列数据并将其写入电子表格中的工作表
It then arranges that data in a way that I need and writes it to a sheet, within my spreadsheet
脚本的最后一部分(不是我自己的作品，而是我在这里找到的)我在网上找到的脚本我已经尝试适应我的需求，但还没有完全适应.创建 PDF，将其保存在谷歌驱动器中，然后通过电子邮件发送.
The last part of the script (not my own work, but something i found here) Script I found online I've tried to adapt for my needs, not quite there yet. Creates a PDF, saves it in google drive then emails it.
这部分需要做更多的工作，因为我想使用名称和日期指定 PDF 的名称.另外我想指定它在谷歌中的保存位置.最后，脚本只生成一个 PDF.想要最终复制脚本，以便我可以创建 1 个 PDF 或批量创建它们.如果我遇到困难，可能会在稍后发布这些内容.
This part requires a bit more work, as I want to specify what the PDF is called using the name and date. Also I'd like to specify where it's saved in google. Lastly the script only produces one PDF. Would like to eventually duplicate the script so I can either create 1 PDF or create them in batches. Will possibly post about these later, if I get stuck.
无论如何，这就是概述.
So anyways that is the overview.
目前该脚本可以运行，并且可以查询我想要的数据，将其写入工作表，将其保存为 PDF 格式并将其通过电子邮件发送到一个硬编码的电子邮件地址.太棒了.
Currently the script works and can query the data I want, write it to a sheet, save it to drive as PDF and email it to a single hard-coded email address. Awesomeness.
但我随后尝试添加一个名为 clearRanges 的函数，它会在写入数据之前清除模板表.我使用名称范围来定义要清除的 3 个部分.但是自从引入它以来，我已经在脚本的各个部分尝试过它.我在驱动器和电子邮件中收到空白 PDF.
But I then tried to add a function called clearRanges which would clear out the template sheet before writing data. I used name ranges to define the 3 sections to clear. But since introducing it, and i've tried it in various parts of my script. I'm getting blank PDF's in my drive and by email.
这就像在清除数据之前不等待创建 PDF 或发送电子邮件一样.我也试过把它放在我的脚本的开头，但同样的事情.不知道为什么.
It's like it's not waiting for the PDF to be created or email to be sent before clearing data. I've tried to put it at the start of my script too, but same thing. Got no idea why.
我正在尝试使用 lock 和 waitlock 作为可能的解决方案，但似乎没有帮助.
I was playing around with lock and waitlock as a possible solution, but it didn't seem to help.
如果有人能帮忙，我将不胜感激.
If anyone can help out, I'd appreciate it.
function getAgentName() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
Browser.msgBox("Please go to the sheet called PDF Creator, in cell A2, choose the agent you wish to create a PDF for");
var sheet = ss.getSheetByName("PDF Creator");
var range = sheet.getRange("A2")
var value = range.getValue();
if (value == 0) {
Browser.msgBox("You need to go to the sheet named PDF Creator and put an agent name in cell A2");
} else {
getAgentData(value);
}
}
function getAgentData(value) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("Form responses 1")
var sourceRange = sourceSheet.getDataRange();
var sourceValues = sourceRange.getValues();
var agentData = [];
var commentsData = [];
for (i = 0; i < sourceValues.length; i++) {
// Defines the data layout for PDF.
var agentName = sourceValues[i][2];
var dateTime = sourceValues[i][3];
var callType = sourceValues[i][7];
var opening = sourceValues[i][8];
var rootCause = sourceValues[i][9];
var rootFix = sourceValues[i][10];
var process = sourceValues[i][11];
var consumer = sourceValues[i][12];
var control = sourceValues[i][13];
var wrapup = sourceValues[i][14];
var dpa = sourceValues[i][15];
var score = sourceValues[i][22];
var comments = sourceValues[i][16];
var agentRow = [dateTime, callType, opening, rootCause, rootFix, process, consumer, control, wrapup, dpa, score];
var commentsRow = [dateTime, comments];
if (agentName == value && agentData.length < 9) {
agentData.push(agentRow)
commentsData.push(commentsRow)
}
}
agentData.sort(function (a, b) {
return b[0] - a[0]
});
commentsData.sort(function (a, b) {
return b[0] - a[0]
});
var destSheet = ss.getSheetByName("AgentPDF");
destSheet.getRange("A1").setValue(value + "'s Quality Score card");
var range = destSheet.getRange(6, 1, agentData.length, agentData[0].length);
range.setValues(agentData);
var commentRange = destSheet.getRange(18, 1, commentsData.length, commentsData[0].length);
commentRange.setValues(commentsData);
emailSpreadsheetAsPDF();
}
/* Send Spreadsheet in an email as PDF, automatically */
function emailSpreadsheetAsPDF() {
// Send the PDF of the spreadsheet to this email address
var email = "firstname.lastname@domain.co.uk";
// Subject of email message
// The date time string can be formatted in your timezone using Utilities.formatDate method
var subject = "PDF Reports - " + (new Date()).toString();
// Get the currently active spreadsheet URL (link)
// Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Email Body can be HTML too with your logo image - see ctrlq.org/html-mail
var body = "PDF generated using code at ctrlq.org from sheet " + ss.getName();
var url = ss.getUrl();
url = url.replace(/edit$/, '');
/* Specify PDF export parameters
// From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
exportFormat = pdf / csv / xls / xlsx
gridlines = true / false
printtitle = true (1) / false (0)
size = legal / letter/ A4
fzr (repeat frozen rows) = true / false
portrait = true (1) / false (0)
fitw (fit to page width) = true (1) / false (0)
add gid if to export a particular sheet - 0, 1, 2,..
*/
var url_ext = 'export?exportFormat=pdf&format=pdf' // export as pdf
+ '&size=a4' // paper size
+ '&portrait=1' // orientation, false for landscape
+ '&fitw=true&source=labnol' // fit to width, false for actual size
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false' // do not repeat row headers (frozen rows) on each page
+ '&gid=928916939'; // the sheet's Id
var token = ScriptApp.getOAuthToken();
// var sheets = ss.getSheets();
//make an empty array to hold your fetched blobs
var blobs = [];
// for (var i=0; i<sheets.length; i++) {
// Convert individual worksheets to PDF
// var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
var response = UrlFetchApp.fetch(url + url_ext, {
headers: {
'Authorization': 'Bearer ' + token
}
});
//convert the response to a blob and store in our array
blobs[0] = response.getBlob().setName("Tester " + '.pdf');
// }
//create new blob that is a zip file containing our blob array
// var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip');
var test = DriveApp.createFile(blobs[0]);
//optional: save the file to the root folder of Google Drive
DriveApp.createFile(test);
// Define the scope
Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
// If allowed to send emails, send the email with the PDF attachment
if (MailApp.getRemainingDailyQuota() > 0)
var lock = LockService.getScriptLock();
GmailApp.sendEmail(email, subject, body, {
attachments: [test]
});
lock.waitLock(20000);
lock.releaseLock();
clearRanges();
}
function clearRanges() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getRangeByName('Header').clearContent();
ss.getRangeByName('Scores').clearContent();
ss.getRangeByName('Comments').clearContent();
}
推荐答案
你可以试试添加 SpreadsheetApp.flush();在调用 emailSpreadsheetAsPDF();
Can you try adding SpreadsheetApp.flush();
around line 60 before calling emailSpreadsheetAsPDF();
commentRange.setValues(commentsData);
SpreadsheetApp.flush();
emailSpreadsheetAsPDF();
我以前也遇到过类似的问题，而且这个方法奏效了.
I've faced a similar problem before and this worked.
这篇关于脚本过早清除数据，有谁知道为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
