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?
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.