3

I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function.

For example on Sheet 2, I have dates and values from form submissions on our website. Here's an example output:

Date          Value
9/15/2019     1
9/16/2019     2
9/18/2019     1

As you can see, 17th of September is missing since there were no form submission on that day. However, I'd like to include days with no submissions to the Sheet. Here's desired output:

Date          Value
9/15/2019     1
9/16/2019     2
9/17/2019
9/18/2019     1

I have the QUERY function in Sheet 2 A1 and it needs to stay there since the raw data on Sheet 1 is automatically updated every day. If it's possible, I would like to have the desired output to Column F on Sheet 2.

Can anyone help me out with this one?


Edit:

screenshot&example

You can see my current QUERY from the screenshot above. In this QUERY, the contacts sheet is the raw data sheet. Columns A:D is the result from QUERY and columns G:J is the result I would like to achieve. In my exmaple (Columns G:J) I've higlighted two rows. As you can see from Columns A:D there are now data from 9/6/2019 and 9/7/2019. Adding the missing dates like this is what I'm trying to achieve.

4
  • 1
    Welcome. Please share a link to a test sheet with efforts and expected result. Also, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. Sep 20, 2019 at 14:21
  • @marikamitsos There isn't any answer to accept yet.
    – Rubén
    Sep 20, 2019 at 14:34
  • @Rubén Preventing is better than treating Sep 20, 2019 at 14:44
  • @marikamitsos That isn't the philosophy of this site, otherwise reading the whole help center would be mandatory before posting anything.
    – Rubén
    Sep 20, 2019 at 15:33

1 Answer 1

2

It looks like you are looking for a single formula solution but they could be hard to understand and maintain, specially if you aren't familiar on how the Google Sheets related features works.

Related features

  1. Dates. Google Sheets handles dates as serialized numbers, where a day is one unit.
  2. Because of the above feature we could create a list of dates by using ROW(A:A) and applying a date format (click on menu Format > Number then the desired date format).
  3. Arrays. Look for the help article Using Arrays in Google Sheets on https://support.google.com/docs. Please note that if your spreadsheet use comma , as decimal separator, instead of using it as the column separador, use a backslash \.
  4. QUERY. On the SQL statement is uses A, B, C notation for columns name when a reference is used as the first argument, but it uses Col1, Col2, Col3 notation for column names when an array is used as the first argument.

Simple Alternative

  1. Remove the blank rows from Sheet1.
  2. Append a list of dates to the source data from Sheet1. Example

    =ArrayFormula({Sheet1!A:B;Sheet1!A2+ROW(A1:A30),IFERROR(ROW(A1:A30)/0,)})
    
  3. On your QUERY formula use the resulting range of the above formula as the first argument.

If you really only want to add the missing dates, instead of the above formula use:

=ArrayFormula(
  FILTER(
    {Sheet1!A2+ROW(A1:A30),IFERROR(ROW(A1:A30)/0,)},
    ISNA(MATCH(Sheet1!A2+ROW(A1:A30),Sheet1!A:A,0))
  )
)

"Complex" Alternative

  1. Remove the blank rows from Sheet1.
  2. Use {Sheet1!A:B;Sheet1!A2+ROW(A1:A30),IFERROR(ROW(A1:A30)/0,))} as the first argument of QUERY, on the second argument (SQL statement) change the columns names from A, B, C notation to Col1, Col2, Col3 notation. The resulting formula will look like the following:

    =ArrayFormula(
      QUERY(
        {Sheet1!A1:B30;Sheet1!A2+ROW(A1:A30),IFERROR(ROW(A1:A30)/0,)},
        "SELECT Col1,Col2"
      )
    )
    

    or

    =ArrayFormula(
      QUERY(
        FILTER(
          {Sheet1!A2+ROW(A1:A30),IFERROR(ROW(A1:A30)/0,)},
          ISNA(MATCH(Sheet1!A2+ROW(A1:A30),Sheet1!A:A,0))
        ),
       "SELECT Col1,Col2"
      )
    )
    

Remarks

  • For simplicity, Sheet!A2+ROW(A1:A30) is used to add 30 dates after the first date, if you need more, use a taller reference.

  • If on your SQL statement you are using the GROUP BY clause, use the first formula as the first QUERY argument.

  • Instead of A1:A30 we could use A:A but we also use way to limit the size of this reference. One alternative is to use ARRAY_CONSTRAIN.

Related

1
  • Thank you very much @Rubén for the input. However, I'm struggling to achieve the desired result with your suggestions. I've edited my post and included more detailed explanation of what I'm trying to achieve. I hope you would take a look at it and maybe help me out again if you can. Thank you! :)
    – Mike
    Sep 23, 2019 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.