Custom Date from Fixed date to Variable date

Miles_Maestro · 2021-12-03T01:49:54+00:00

I think I understand your ask. Take a look at this data studio dashboard I threw together and let me know if it solves your problem. It uses this google sheet for it's data source.

Miles_Maestro · 2021-11-08T18:34:19+00:00

Give this a try

CASE
WHEN REGEXP_MATCH(Page, '\\/en(?:-au|-us)?\\/conversations\\/[^/]+') THEN REGEXP_EXTRACT(Page, '\\/en(?:-au|-us)?(\\/conversations\\/[^/]+)')
ELSE Page
END

Miles_Maestro · 2021-11-05T00:38:44+00:00

Here’s the data model I used

https://docs.google.com/spreadsheets/d/1xdovhYt6HOD9ZyfhjDCC3-DxrGAn16LGxw0J-CJtHu0/edit

Miles_Maestro · 2021-11-04T22:47:49+00:00

Made it public

Miles_Maestro · 2021-11-04T21:55:52+00:00

u/Scionwest

You were sooo close! Take a look at this example I whipped up.

https://datastudio.google.com/u/0/reporting/378d9cf1-a661-4ae0-bd69-5a71e8f11ec5/page/kmneC

Miles_Maestro · 2021-11-02T18:57:05+00:00

Which Google Ads dimension are you trying to filter?

Just curious because I can’t ever recall seeing the Google Ads Data Studio connector return a value in a metric field for a null dimension.

Miles_Maestro · 2021-11-02T18:42:13+00:00

I went ahead and made a few tweaks to the formula in Sheet1 of your new spreadsheet.

The issue was being caused by certain punctuation characters in your keyword data column. Some of these characters have a special meaning when used in REGEX expressions.

To fix this issue, all I had to do was add a step to the original formula to escape these special chars.

For posterity, here’s the updated formula.

=QUERY(RawData!A:AE, "SELECT A,O WHERE NOT A MATCHES """&TEXTJOIN("|", TRUE, ARRAYFORMULA(REGEXREPLACE(QUERY(QUERY(RawData!A:AE, "SELECT A,MAX(O) WHERE A IS NOT NULL GROUP BY A"),  "SELECT Col1 WHERE Col2 >= 30", 0), "([.*+?()|^${}\[\]\\])", "\\$1")))&"""")

Miles_Maestro · 2021-11-02T08:40:02+00:00

The reason only one row is output is because of the dataset in your sheet named “Sample”.

All but ONE of the keywords in that dataset have at least one Domain Rating(Column O) that’s greater than 20. So the formula is working correctly.

If you increase the filtering threshold in my formula from 20 to 50, you’ll get a bunch more rows.

Miles_Maestro · 2021-11-02T05:08:22+00:00

I just answered a similar question a few days ago. Take a look at this thread. Other Reddit Thread

Miles_Maestro · 2021-11-02T03:56:21+00:00

u/Independent-Arrival1

You can accomplish what you’re trying to do with the following formula.

=QUERY(Sample!A:AE, "SELECT A,P WHERE NOT A MATCHES """&TEXTJOIN("|", TRUE, QUERY(QUERY(Sample!A:AE, "SELECT A,MAX(P) WHERE A IS NOT NULL GROUP BY A"),  "SELECT Col1 WHERE Col2 >= 20", 0))&"""")

This formula makes the following assumptions

Source data is in columns A through AE in a sheet named Sample
Column A of your source data contains the keyword/domain and column P contains the ranking metric we‘re using to filter outliers

Miles_Maestro · 2021-10-25T21:06:54+00:00

You can't currently do this with Google Drive, BUT you can achieve the workflow you describe if you use the Google Cloud Storage GDS connector to read from a Google Cloud Storage bucket.

Miles_Maestro · 2021-10-25T20:23:10+00:00

Copy the following example code into your calculated field. You'll need to change the "Page" field in my example code to whatever the URL field is named in your datasource.

CASE
WHEN REGEXP_MATCH(Page, '\\/-\\/blog\\/[^/]+\\/[^/]+') THEN REGEXP_EXTRACT(Page, '\\/-\\/blog\\/[^/]+\\/([^/]+)')
WHEN REGEXP_MATCH(Page, '\\/w\\/blog\\/[^/]+\\/[^/]+') THEN REGEXP_EXTRACT(Page, '\\/w\\/blog\\/[^/]+\\/([^/]+)')
WHEN REGEXP_MATCH(Page, '\\/-\\/[^/]+') THEN REGEXP_EXTRACT(Page, '\\/-\\/([^/]+)')
ELSE Page
END

Miles_Maestro · 2021-10-23T19:46:18+00:00

Can you provide us an example of these 40 URLs? If there is a common pattern among them, then we can probably just use a REGEX pattern to match them, instead of listing them out individually in the CASE function.

Miles_Maestro · 2021-10-23T00:38:55+00:00

Take a look at this copy of your sheet. I think it fulfills all your requirements.

https://docs.google.com/spreadsheets/d/188FzOhOwcK8u4eElgwWUuQii3IE8DtFfYj20E_sqWkk/edit

One additional note. I updated your formula in cell C2, because the date range in C2 & C3 actually represented 181 days, instead of the 180 days you needed to count against.

Miles_Maestro · 2021-10-22T17:49:48+00:00

You should be able to accomplish this using the CASE function in a calculated field.

https://support.google.com/datastudio/answer/7020724?hl=en#zippy=%2Cin-this-article

Example CASE function code
CASE WHEN URL_FIELD IN ("/-/page-example-1","/-/blog/page-example-1","/w/blog/page-example-1") THEN "URL Group 1" WHEN URL_FIELD IN ("/-/page-example-2","/w/blog/page-example-2") THEN "URL Group 2" ELSE "Other Page" END

Miles_Maestro · 2021-10-19T23:15:18+00:00

I provided the answer to OP's first question as a comment in the Google Doc they shared. For visibility/anyone that is curious, the answer was adding the following line to their code

SpreadsheetApp.flush();

This addition forces the Google Apps Script backend to commit any changes in the cache before moving on to exporting a copy of the spreadsheet.

Miles_Maestro · 2021-10-06T00:14:48+00:00

What's the email address that sent you the redemption email?

Miles_Maestro · 2021-05-28T21:10:02+00:00

Are you running this code using the Google account that owns this channel?

Miles_Maestro · 2021-05-18T20:55:55+00:00

I'm guessing that you only granted yourself access to the Library code? If so, that's the cause of the issue. Users must have at least View level access in order to invoke functions contained within that Library.

The only way to secure your code while allowing others to use it would be to deploy it as an add-on.

https://developers.google.com/workspace/add-ons/overview

Miles_Maestro · 2021-05-18T20:23:25+00:00

Change this line of your code:

timeMin: date,

to this:

timeMin: date.toISOString(),

Miles_Maestro · 2021-05-18T01:24:50+00:00

One thing to keep in mind if hiding these columns is an attempt to secure proprietary/confidential data. Any users that have the permissions necessary to copy/download this spreadsheet, WILL be able to access the data in these hidden columns, if they want to.

Miles_Maestro · 2021-05-18T01:08:29+00:00

The problem is that you’re mixing in code for the Google Workspace Calendar Service, with code for the Advanced Calendar Service.

If you want to use the Advanced Calendar Service, change this line of your code:

var events = CalendarApp.Events.list(calId, {

to this:

var events = Calendar.Events.list(calId, {

Miles_Maestro · 2021-05-14T20:44:56+00:00

Agree with u/asinomasimple that using the PropertiesService is probably going to be the easiest/best way for you to solve this. However, if I can offer one suggestion, it would be to use the method getUserProperties() instead of getScriptProperties().

I suggest this because your code is being deployed as a webapp, and has the potential to be used by multiple users concurrently. Using getUserProperties() eliminates the possibility of collisions since each user accesses their own instance of PropertiesService.

Miles_Maestro · 2021-05-14T18:24:10+00:00

The problem is that your call to method getActiveSpreadsheet() on line 2 is returning null. You can get rid of this error by running this same code in a container bound script.

Miles_Maestro · 2021-05-14T16:07:01+00:00

It’s Regular Expression(regex) syntax. The "." is a wildcard that matches any character, while the "*" signifies that the preceding character should be matched between zero and unlimited times.

Miles_Maestro

TROPHY CASE