Google-sheets – Adding missing dates to Google Sheets

google sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-querygoogle-sheets-timestamp

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.

Best Answer

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

Related Topic