复制电子表格/时间触发器不起作用，但手动触发器效果很好

Question

只是想知道是否有人可以帮助我？我是应用程序脚本的新手，甚至不是专业开发人员。

我下面的代码的目的是将Android应用程序的数据从Appsflyer拉到ExportTemp工作表中，然后将数据行复制到另一个工作表Daily_Android的最后一行，然后删除任何重复的行。 然后对iOS数据执行相同的操作。

当我手动运行代码时，它运行良好，但是克隆GoogleSheet()函数在时间触发时不起作用，尽管日志说它运行成功，但AppsflyerExport()函数确实运行良好。 也就是说，Appsflyer 数据被拉入 ExportTem 工作表，但在时间触发时(每天一次)未复制到Daily_Android表中。

多谢

杰森

function AppsflyerMain(){
var spreadSheetID = '******';
var spreadSheet = SpreadsheetApp.openById(spreadSheetID);
SpreadsheetApp.setActiveSpreadsheet(spreadSheet);
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var from = new Date(now.getTime() - 1 * MILLIS_PER_DAY);
var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);
//export Android 
var fromDate = Utilities.formatDate(from, 'Asia/Jakarta', 'yyyy-MM-dd');
var toDate = Utilities.formatDate(to, 'Asia/Jakarta', 'yyyy-MM-dd');
var appID = '****';
var apiToken = '****';
var reportType = 'daily_report';
var sourceSheetName = 'ExportTemp';
var datastoreSheetName = 'Daily_Android';
var sheet_andr = spreadSheet.getSheetByName(sourceSheetName);
if (sheet_andr == null) {
sheet_andr = spreadSheet.insertSheet(sourceSheetName);
}
AppsflyerExport(sheet_andr,appID, apiToken,reportType,fromDate,toDate);
cloneGoogleSheet(spreadSheetID, spreadSheetID, sourceSheetName,datastoreSheetName);
removeDuplicates(spreadSheetID,datastoreSheetName);
//export iOS
appID = '****';
apiToken = '*****';
reportType = 'daily_report';
sourceSheetName = 'ExportTempiOS';
datastoreSheetName = 'Daily_iOS';
var sheet_ios = spreadSheet.getSheetByName(sourceSheetName);
if (sheet_ios == null) {
sheet_ios = spreadSheet.insertSheet(sourceSheetName);
}
AppsflyerExport(sheet_ios,appID, apiToken,reportType,fromDate,toDate);
cloneGoogleSheet(spreadSheetID, spreadSheetID, sourceSheetName,datastoreSheetName);
removeDuplicates(spreadSheetID,datastoreSheetName);
}
function AppsflyerExport(sheet,appID,apiToken, reportType,fromDate, toDate){
request_url = '=importdata("https://hq.appsflyer.com/export/'+appID+'/'+reportType+'/v5?api_token='+ apiToken + '&from='+fromDate+'&to='+toDate+'")';
sheet.clear({contentsOnly: true});
sheet.getRange(1, 1).setValue(request_url); 
}
function cloneGoogleSheet(ssAID,ssBID,sourceSheet,targetSheet) {
var ssA = SpreadsheetApp.openById(ssAID);
var ssB = SpreadsheetApp.openById(ssBID);
// source sheet
var ss = ssA.getSheetByName(sourceSheet);
if (ss == null) {
ss = ssA.insertSheet(sourceSheet);
}
// Get full range of data, exclude first row the title row
var SRange = ss.getRange(2, 1, ss.getMaxRows(), ss.getMaxColumns());
// target sheet
var ts = ssB.getSheetByName(targetSheet); 
if (ts == null) {
ts = ssB.insertSheet(targetSheet);
}
var TRange = ts.getDataRange();
var ts_numRows = TRange.getNumRows();
var ts_nextRow = ts_numRows +1;
Logger.log('last row of ' + targetSheet + ' is ' + ts_numRows);
console.log('last row of ' + targetSheet + ' is ' + ts_numRows);
//copy source data to last row of target sheet
SRange.copyTo(ts.getRange(ts_nextRow, 1));
Logger.log('copied new data from row '+ts_nextRow);
console.log('copied new data from row '+ts_nextRow);
};

function removeDuplicates(ssID, sheetName) {
var ss = SpreadsheetApp.openById(ssID);
var sheet = ss.getSheetByName(sheetName);
var data = sheet.getDataRange().getValues();
var newData = [];
for (var i in data) {
var row = data[i];
var duplicate = false;
for (var j in newData) {
if (row.join() == newData[j].join()) {    // this is to check the duplicate of whole row
//if(row[0] == newData[j][0] ){
duplicate = true;
}
}
if (!duplicate) {
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Answer 1

您是否意识到您要两次发送相同的工作表ID来克隆GoogleSheet？因此在该函数中将其打开两次作为两个不同的名称？