Adding missing dates to Google Sheets

1
votes

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

Here's the result of the QUERYon Sheet 2:

Screenshot

Columns A:D is the current output. As you can see, there are several days missing since there was no form of submission on those days. However, I'd like to include days with no submissions to the Sheet 2 also.

Columns G:J is the desired output. As you can see from Columns G:J I've manually included data from 9/6/2019 and 9/7/2019. Adding the missing dates like this is what I'm trying to achieve. In addition to missing dates, the values of "Order Total", "Price Estimate Language" and "Price Estimate Status" needs to be included also.

Can anyone help me out with this one? I'm totally lost with this, so, unfortunately, I don't have any formulas that I've tried to show you.

Edit:

bellow suggestion works perfectly if I create columns A:D manually:

Manual Input

However, if I try to use the formula with the original QUERY, I get #VALUE with an error message saying "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.":

Error message with <code>QUERY</code>

Any ideas on how to make this work without manually typing columns A:D? Thank you.

1
google-sheetsfiltergoogle-sheets-formulaarray-formulasgoogle-sheets-query

1 Answers

0
votes
=ARRAYFORMULA(SUBSTITUTE(QUERY({A1:D; {SPLIT("♦♥♦"&FILTER(ROW(
 INDIRECT(MIN(B2:B)&":"&MAX(B2:B))), 
 NOT(COUNTIF(B2:B, ROW(INDIRECT(MIN(B2:B)&":"&MAX(B2:B))))))&"♦♥♦♥", "♦")}}, 
 "where Col2 >= date '2019-9-1' 
    and Col2 <= date '2019-9-8' 
  order by Col2", 1), "♥", ))

0

UPDATE:

={"Order Total", "Price Estimate Date", "Price Estimate Language", "Price Estimate Status"; 
 ARRAYFORMULA(SORT({{TO_DATE(SPLIT("♦ ♦"&FILTER(ROW(
 INDIRECT(ROUNDDOWN(MIN(contacts!E2:E))&":"&ROUNDDOWN(MAX(contacts!E2:E)))), 
 NOT(COUNTIF(QUERY(contacts!E2:E, "where E != 'N/A'"), ROW(
 INDIRECT(ROUNDDOWN(MIN(contacts!E2:E))&":"&ROUNDDOWN(MAX(contacts!E2:E)))))))&"♦ ♦ ", "♦"))};
 FILTER({contacts!B2:B, contacts!E2:E, contacts!F2:F, contacts!G2:G}, 
 contacts!E2:E<>"N/A", contacts!E2:E<>"")}, 2, 1))}

0