Lazy Big Query script

library(googleAuthR)
library(googleAnalyticsR)
library(tidyverse)
library(bigQueryR)
library(rvest)
library(lubridate)

# Last Financial Week
end_date <- floor_date(Sys.Date(),"week") - 1
start_date <- end_date - 6

# service json privilege and path
gar_set_client("C:/path/client_secret.json", scopes= c("https://www.googleapis.com/auth/webmasters",
                                                       "https://www.googleapis.com/auth/analytics",
                                                       "https://www.googleapis.com/auth/bigquery"))

gar_auth(email = "[email protected]")

# get the variables from google table
url <- "https://support.google.com/analytics/answer/3437719?hl=en"
bigQueryTable <- url %>%
  read_html() %>%
  html_nodes(xpath='//table[@class="nice-table compact-table"]') %>%
  html_table()
bigQueryExport <- bigQueryTable[[1]]

# clean table
bigQueryExport <- bigQueryExport[!grepl("This field is deprecated", bigQueryExport$`Field Name`),]
bigQueryExport$`Field Name` <- gsub("\\n((?<=[\\s])\\s*|^\\s+|\\s+$)","", bigQueryExport$`Field Name`, perl=TRUE)

# transform every row in variable so is easier to find while writing the query
for(i in 1:nrow(bigQueryExport)){
  assign(paste0(bigQueryExport[i,1]),bigQueryExport[i,1])
}

# run example output
hits.transaction.transactionId

# list of projects
projects <- bqr_list_projects()

# get project 1
my_project <- projects[1]

# set it as default
bqr_global_project(as.character(my_project))

# get dataset list 
datasets <- bqr_list_datasets()

# first dataset
dataset <- datasets[1,1]

# build query using the variables created
query <- paste0("SELECT
  ",as.character(channelGrouping),",
  COUNT(DISTINCT CONCAT(",as.character(fullVisitorId),", CAST(",as.character(visitStartTime)," AS STRING))) AS Sessions,
FROM
  `",as.character(my_project),".",as.character(dataset1),".ga_sessions_*`, UNNEST(hits) as hits
WHERE
  _TABLE_SUFFIX BETWEEN '",as.character(format(start_date,'%Y%m%d')),"'
  AND '",as.character(format(end_date,'%Y%m%d')),"'
  AND totals.visits >= 1
GROUP BY ",as.character(channelGrouping),
                " ORDER BY 2 DESC")

## run query
fistTableQuery <- bqr_query(projectId = as.character(my_project), datasetId = dataset1, 
                            query,
                            useLegacySql = FALSE)