Concatenating with line breaks, with blank columns...

Aadam · 12-28-2021 11:42 PM

I have a formula which concatenates queries (from construction company to employer) so that it appears with line breaks in a workflow pdf report. I used to have the queries just in one query column, but I am now using a Hyperlink to google form response for the client to be able to respond from the workflow email. The problem is I can’t figure out how to keep line breaks as the url is referencing to the cell value (so in the google sheet cell there could be Query1 line break Query 2 line break Query 3 etc). It’s working now the way I want it to, but how do I adapt formula to ignore lets say query5 to query10 if there is no data filled in there? This is so that I don’t get unecessary spaces below queries in my reports. Note. Currently the queries build from 1, so if there is 3 queries then there will be Query1, Query2, Query3 blank columns for the remaining Query4 to Query10.

Appsheet Formula currently using below;

CONCATENATE( [QUERY1],"
“,[QUERY2],”
“,[QUERY3],”
“,[QUERY4],”
“,[QUERY5],”
“,[QUERY6],”
“,[QUERY7],”
“,[QUERY8],”
“,[QUERY9],”
",[QUERY10])

Apolgies I found a few topics covering line breaks, but could not find how to ignore blank “columns”

jyothis_m

@Aadam Can you please try this and check whether it helps

CONCATENATE(IF(ISNOTBLANK([QUERY1],([QUERY1],"
“),"")),IF(ISNOTBLANK([QUERY2],([QUERY2],"
“),"")),IF(ISNOTBLANK([QUERY3],([QUERY3],"
“),"")),IF(ISNOTBLANK([QUERY4],([QUERY4],"
“),"")),IF(ISNOTBLANK([QUERY5],([QUERY5],"
“),"")),IF(ISNOTBLANK([QUERY6],([QUERY6],"
“),"")),IF(ISNOTBLANK([QUERY7],([QUERY7],"
“),"")),IF(ISNOTBLANK([QUERY8],([QUERY8],"
“),"")),IF(ISNOTBLANK([QUERY9],([QUERY9],"
“),"")),IF(ISNOTBLANK([QUERY10],([QUERY10],"
“),"")))

Aadam

Hi Jyothis, thanks for the response.

After a few attempts I noticed that the workflow pdf report and the workflow email template (where formula is in table) was doing it own cropping and not extending past empty columns, so I have left the formula as is without the IF(ISNOTBLANK) so that the formula stay a bit smaller.

Screenshot below of report;

I picked up another issue in the google form it was taking the number 10 as being 1 in the url link. So I reduced to 9 queries only.
https://docs.google.com/formsblahblah918=A1 A2 A3 A4 A5 A6 A7 A8 A9 (%0A%0AA10 This last part was giving a 1 value. in google form and giving me the same query as text value in Query 1)

I think this is a question for another forum.

Steve

Try this:

SUBSTITUTE(
  CONCATENATE(
    LIST(
      [QUERY1],
      [QUERY2],
      [QUERY3],
      [QUERY4],
      [QUERY5],
      [QUERY6],
      [QUERY7],
      [QUERY8],
      [QUERY9],
      [QUERY10]
    )
    - LIST("")
  ),
  " , ",
"
"
)

Concatenating with line breaks, with blank columns ignored (data in ascending order 1 to 10)