google sheets-analytics weekly query

0
votes

I'm trying to create a query in google sheets that will pull data for the week so far. I can use the NDaysAgo filter to get the last 7 days but what I really need is from Monday to Sunday, and I don't want to have to go and edit the sheets every week. Also, if this can be done for weekly, I'd use the same formula for a month-to-date solution as well.

Thanks!

1
google-apps-scriptgoogle-analyticsgoogle-sheetsgoogle-analytics-api

1 Answers

0
votes

I am going to assume you are doing this in app Script.

Remember app script is just JavaScript. The following will return the previous Monday for a date. 

var d = new Date();  
var day = d.getDay(),
diff = d.getDate() - day + (day == 0 ? -6:1); // adjust if day is Sunday
Logger.log( new Date(d.setDate(diff)));

Use the getDay method of Date objects, you can find the number of the day of the week (being 0=Sunday, 1=Monday, etc).

You can then subtract that number of days plus one. result was:

[15-01-20 08:56:33:691 CET] Mon Jan 19 08:56:33 GMT+01:00 2015