Adam, if I will have multiple tables pulled from BigQuery and multiple Pivot Tables, will the user need to update each Pivot Table separately, or is the data refresh global?
Nope there is a scheduled data refresh if you create the pivots from the live data where you can choose to refresh all artefacts also (graphs / pivots tables etc) you can do it on a trigger
var query = `SELECT extract(year from date) as year, category_name, item_description, vendor_name, COUNT(*) as num_trans, ROUND(SUM(sale_dollars),2) as sales_in_usd, ROUND(SUM(state_bottle_cost * bottles_sold),2) as cost FROM \`bigquery-public-data.iowa_liquor_sales.sales\` Where EXTRACT(year FROM date) IN (${input.var1}, ${input.var2}) AND category_name IN ('${input.var3}','${input.var4}','${input.var5}') AND city = '${input.var6}' Group by ALL Order BY 5 desc;`
@@analyticswithadam I want use on connect sheet and params in a CELL with WITH. I use sql select * from `a.example1.b` WHERE column_name IN (@param) Is this sql correct? And value of cell I use comcomma example a,b,c,d
Awesome video thank you
This seems simple but actually helps a lot
Adam, this is amazing, would it be possible to schedule updates or just applying general workflows using appscript?
You can schedule updates on the sheet
Hello Adam. Is there any maximum rows of data in google sheet like excel ?
After about 100k rows it craps itself
Adam, if I will have multiple tables pulled from BigQuery and multiple Pivot Tables, will the user need to update each Pivot Table separately, or is the data refresh global?
Nope there is a scheduled data refresh if you create the pivots from the live data where you can choose to refresh all artefacts also (graphs / pivots tables etc) you can do it on a trigger
thanks
How to use IN in sql parames query connect sheet. Thanks bro
var query = `SELECT extract(year from date) as year, category_name, item_description, vendor_name, COUNT(*) as num_trans, ROUND(SUM(sale_dollars),2) as sales_in_usd, ROUND(SUM(state_bottle_cost * bottles_sold),2) as cost FROM \`bigquery-public-data.iowa_liquor_sales.sales\` Where EXTRACT(year FROM date) IN (${input.var1}, ${input.var2}) AND category_name IN ('${input.var3}','${input.var4}','${input.var5}') AND city = '${input.var6}' Group by ALL Order BY 5 desc;`
@@analyticswithadam I want use on connect sheet and params in a CELL with WITH.
I use sql
select * from `a.example1.b`
WHERE column_name IN (@param)
Is this sql correct?
And value of cell I use comcomma example
a,b,c,d
select * from `learningbigquery.example1.liil_example`
where CAST(string_field_0 AS STRING) IN UNNEST(SPLIT(@STRING_FIELD_0, ','))
I use sql. It's OK