AWQL / Apps脚本，其中Date Day Name等于Today's Day Name

Question

我正在运行一个基本的AWQL和应用程序脚本查询从我的帐户到谷歌工作表。我目前有一个类似的脚本，今天就在趋势的基础上工作。我正在构建一个自动报告，选择过去30天的竞选表现。然而，由于帐户的大小，我只想选择该时间范围内与今天的日期名称相匹配的日期。例如，今天是星期五，所以我只需要日期范围内星期五的数据。这是一个趋势数据报告，这就是为什么我只想要一周中的当前日期。

下面是推送到电子表格中的代码示例。我永远不能让它在包含WHERE语句的情况下运行。我尝试了许多变体，这些变体让我想到了这个问题。

我收到“解析错误。请检查您的选择器”。错误消息，这是由于where语句造成的，但我正在努力理解原因。

   function main(){

    var sheetURL = 'INSERT_SPREADSHEET_URL_HERE';
    var sheetName = 'googleData';

    //This is the days of the week for the today lookup
    var days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' ];

    //This is today's date
    var end = new Date().toISOString().slice(0,10);

    //This is for x amount of days previous, currently set at 30 days
    var d = new Date();
    d.setDate(d.getDate()-30);
    var start = d.toISOString().slice(0,10);

    //This joins the two above dates together and replaces / with blank characters
    var dateRanges = start.replace(/-/g,"") + "," + end.replace(/-/g,"");

    //This supplies the current day of week
    var dow = new Date(end);
    var dayName = days[dow.getDay()];

    //Check the correct day of week is showing
    //Logger.log(dayName);

    //This is to check the date format is correct
    //Logger.log(dateRanges);

    var QUERIES = [{'query' : 'SELECT Date, DayOfWeek, HourOfDay, CampaignName, Impressions, Clicks, Cost, Conversions, ConversionValue ' +
                'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
                'WHERE DayOfWeek = ' + dayName +
                'DURING ' + dateRanges, 
                'spreadsheetUrl' : + sheetURL,
                'tabName' : 'googleData',
                'reportVersion' : 'v201806'
               }            
              ];


      //This is to gather the above query and push it to the spreadsheet stated above
     for(var i in QUERIES) {
        var queryObject = QUERIES[i];
        var query = queryObject.query;
        var spreadsheetUrl = queryObject.spreadsheetUrl;
        var tabName = queryObject.tabName;
        var reportVersion = queryObject.reportVersion;
        //Logger.log(spreadsheetUrl + " " + query);
        var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
        var sheet = spreadsheet.getSheetByName(tabName);
        var report = AdWordsApp.report(query, {apiVersion: reportVersion});
        report.exportToSheet(sheet);
      }
}

Answer 1

检查您的WHERE子句，dayName值可能应该用引号括起来。

试试这个(注意我使用了单引号而不是双引号)：

var query = "SELECT Date, DayOfWeek, HourOfDay, CampaignName, Impressions, Clicks, Cost, Conversions, ConversionValue "
        + "FROM CAMPAIGN_PERFORMANCE_REPORT" 
        + "WHERE DayOfWeek = '" + dayName + "'"
        + "DURING " + dateRanges;

var QUERIES = [{
        query:query, 
        spreadsheetUrl: sheetURL, 
        tabName:"googleData", 
        reportVersion:"v201806"
    }];

提示：将来，请考虑使用Utilities.formatDate()，而不是分割日期字符串或使用几天的查找表。