It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Is it possible to use an attachment with a GetFile template to pass parameters in PocketQuery?

I want to have users upload a file to a confluence page. Once uploaded, a template (if necessary) can call the GetFile process when executing a query and look at a column as a parameter to pass in that query. Is this able to be done in the system?

1 answer

Okay, so you want to do the following?

  • Upload an attachment to a Confluence page.
  • In one of your querys call $PocketQuery.executeQuery("getAttachment") which calls the query "getAttachment" which gets the attachment and parses it for a parameter. (Depending on the attachment you might need a converter for this)
  • Use the result of $PocketQuery.executeQuery("getAttachment") in your current query as a parameter.

Yes, this is absolutely possible. But when using executeQuery() / renderPocketQueryMacro() you should always keep in mind that you are executung extra queries, so it might be a good Idea to activate dynamic load for your macro.

Essentially, you outlined it to a T. What I need to do is have users be able to upload either a .csv file or a .xlsx file to the confluence page with a specific name. Within their sheet, there is a column that will contain the parameter(s) listed out since I am limited to the number of inputs when doing just a dynamic parameter as a String.

When the query runs, it looks at an Oracle database and bumps up against the spreadsheet the users have uploaded to return the results of those values in the spreadsheet. Like a VLOOKUP as an example.

I have not been able to find any details around this type of a template though in the community or in the documentation. I am great at SQL but coding out a template, not so much. Any direction is much appreciated.

Okay, so, step by step - to do this you need two queries:

  • A "get_attachment" query, that fetches the CSV.
  • The "oracle_query" that takes a parameter.

Im jumping over the Database/Query setup, since I guess you already have these configured.

Since your "get_attachment" query gets you a CSV string and PocketQuery can't interpret that as a table (it only knows SQL tables and JSON) you need to write a Converter that parses the CSV and returns a JSON array. Parsing CSV is actually really easy, so your converter shouldn't be very complicated:

// replace with whatever your file uses as a delimiter
var csvDelimiter = ',';

// specify your column names
var columnNames = ['first column', 'second column', 'third column'];

function lineToRow(line) {
  var cells = line.split(csvDelimiter);
  var rowObject = {};

  columnNames.forEach(function(columnName, i) {
    rowObject[columnName] = cells[i];
  });

  return rowObject;
}

function convert(csv) {
  return csv.substring(1, csv.length-1).replace(/\\r/g, '').split('\\n').map(lineToRow);
}

Using this converter and the standard template, this file:

foo,bar,baz
auto,apfel,birne
wolke,cloud,basto

produced this table for me:

table.PNG

Once you got that working you can now write a custom template for your "get_attachment" query that uses some value from the csv table as a parameter for your second query. The template could be as simple as this:

## Getting the parameter from the CSV table.
## This would be the "second column" of the second row -> "apfel".
#set($paramFromCsv = $result.get(1).get("second column"))

## Just for debugging so you can see if you got the correct cell.
<div>Calling "oracle_query" with parameter: "$paramFromCsv"</div>

$PocketQuery.renderPocketQueryMacro("oracle_query"
, {
  
"page": $page,
  
"parameters": { "your_parameter": $paramFromCsv }
})

Hope this helps!

Once you understand how to correctly use $PocketQuery.executeQuery() and $PocketQuery.renderPocketQueryMacro() you'll have a hard time finding anything that you can't do with PocketQuery. :)

If you want to talk about even more details, or send us some of your existing templates for concrete help, I recommend you raise a ticket at our ServiceDesk for PocketQuery. :)

Thanks for the response Sven.

 

I tried this and it was not working. I think I might have explained this wrong. What I am trying to do is allow for the users to upload the attachment. Let's say that attachment contains just one column of records. Those records are acting as the parameter in my SQL query that I have setup in PocketQuery to look at an external database. When executed, I want that query to return the results based on what was in the attached .csv tab delimited text file.

 

I don't have a query set for GetFile or anything like that. All I have is the query for the external database. I have seen that we can use a template to pull in a file which is what your converter example looks like but that is a bit greek to me as I don't understand how these are all tying together.

You mentioned a ServiceDesk ticket. How might I log this or obtain a login if my enterprise already has Confluence licensing?

To raise a ticket at our ServiceDesk please create an account. The account has nothing to do with your Confluence or any of your licenses. It is just a free customer account at our ServiceDesk - you won't be charged or anything like that.

create_account.PNG

Once you have created an account please attach your existing query and template (feel free to censor anything sensitive) and describe exactly what you want to do.

Thank you! :)

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

What's New in Confluence Cloud – July 2020 Edition

Here’s another edition of “What’s New” this month in Confluence Cloud. Quickly (and more easily) adjust permission settings We know that sometimes the layers of space permissions and page restric...

6,463 views 11 30
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you