-1

Good day, I'm trying to add an empty line between 2 queries, to make it easier to look at and format.

Here is the main formula:

={query(filter((importrange("link";"sales!A2:F"));

(importrange("link";"sales!A2:A"))>=(EOMONTH("01.09.18";-1)+1);
(importrange("link";"sales!A2:A"))<=EOMONTH("01.09.18";));

"Select Col1,Col3,Col4,Col5,Col6  where Col2='Manager' label (Col1) 'Date',(Col3) 'Client', (Col4) 'Total', (Col5) 'Date of pay', (Col6) 'Comment' ");

query(filter((importrange("link";"payments!A2:G"));

(importrange("link";"payments!A2:A"))>=(EOMONTH("01.09.18";-1)+1);
(importrange("link";"payments!A2:A"))<=EOMONTH("01.09.18";));

"Select Col1,Col7,Col2,'  ',' ' where Col6='Manager' label (Col1) 'Date',(Col7) 'Client', (Col2) 'Total',' ' ' ' ,'  ' ' '")}

I've added line breaks to make it a little easier to read.

As a potential solution I have tried to mimic what I did in the second part of the formula with non existent columns

=query(importrange("link";"link!A2:F"); "Select ' ', '  ', '   ', '    ' ,'     '  label ' ' ' ', '  ' ' ', '   ' ' ', '    ' ' ', '     ' ' '")

This breaks the code in a way, that only the first query is displayed.

As additional info, these queries return this information:

Date | Client | Sum | Date of payment | Comment

As for the second query

Date | Client | Sum

Any other comments or corrections for the main formula are greatly appreciated, I'm sort of new to this, and might not be using the right tools for the jobs

Konata
  • 275
  • 1
  • 3
  • 14

1 Answers1

2

You can add non-existent columns(say 5) like this:

=ARRAYFORUMLA({QUERY();
               {""\""\""\""\""};
              QUERY()})

Reference:

Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Gives me "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows" – Konata Sep 19 '18 at 10:39
  • First one is 5, followed by 3 and 2 fake ones to avoid ArrayLitreal error – Konata Sep 19 '18 at 10:44
  • Your edit with dashes worked, thank you! Didn't think it would be that simple. Any chance you could explain what is the difference between " , " and " \ " ? – Konata Sep 19 '18 at 10:45
  • 2
    @Konata Read the reference, i added in answer: >`Note: For countries that use commas as decimal separators (for example €1,00), commas would be replaced by backslashes (\) when creating arrays` – TheMaster Sep 19 '18 at 10:50