How can I check if strings match?

user_1005039 0
Viewed 1459

I have a Google Sheet document that I only have read access to.

It has a set of workers in it. One of the fields is for "job location", and another is for "house location". When these fields don't match, the worker is "remote".

I'm trying to add a calculated column to a data source in Google Data Studio, but I can't find any string function that checks for equivalence, and just going J=K doesn't work.

The CASE operator isn't able to compare columns either.

Is there a way to make a formula determine if two fields are equivalent?

google-data-studio
2 Answers

Currently, there is no direct solution in Data Studio to do this.

However, you can take one of two approaches:

  1. Create a new Google Sheet. Use IMPORTRANGE to bring in entire dataset from the source Sheet and then add the comparison column in this worksheet. Use ARRAYFORMULA to extend the formula all the way to the end. (e.g. =ARRAYFORMULA(D:D=E:E) - can be further polished) This Sheet can then work as your data source.

  2. Create a Community Connector to fetch data from the Sheet using the Sheets Service. Add the comparison as a column in Apps Script.

user_40240720

For future reference, the feature was introduced in the 07 Jan 2021 update; thus using the fields specified in the question (job location and house location), the CASE statement below does the trick:

CASE
  WHEN NOT job location = house location THEN "remote"
  ELSE "not remote"
END

Editable Google Data Studio Report and a GIF to elaborate:

user_128925530
Related