Search code examples
google-sheetsgoogle-analytics-apigoogle-sheets-apigoogle-sheets-query

Set start and end date to current month Google Anaytics API

I'm setting up Google Sheet report using the Google Analytics app to generate a custom report, I've spent days searching for info on the subject all over the web for an answer to set current month for the report.

I can set start and end date with no problem, but I want the automated reports to be able to reset to the current month without me having to update the start and end date every month.

Solution

  • To achieve this, use the below:

    For End Date, use 

    today

    or, to make your report upto the previous day:

    yesterday

    For the start date, use the formula below:

    =CONCATENATE(YEAR(today()),"-",
IF(LEN(MONTH(TODAY()))=1,CONCATENATE(0,MONTH(TODAY())),MONTH(TODAY())),
"-01" )

    The formula will concatenate the current year, current month, and 01.

    Another way to approach this problem is through using EOMONTH, for example to get the first day of this month:

    =EOMONTH(today(), -1)+1