Invoices from spreadsheets?

Feature Request
1

Is there an option to populate each line of an invoice from a prepared spreadsheet or CSV file?
I have some rather long lists of items in spreadsheets that I’d like to itemise in Quickfile invoices.
At present it is repetitive cut and paste to build the invoice.

2

Hi @Nigel_Robson

There isn’t a way to do this I’m afraid, but I can see your thread is already in the Feature request category, so we’ll certainly keep an eye on interest here.

The one thing you can do is use the invoice inventory management, which allows you to set up items that can easily be re-used between invoices. There’s more on this here.

Where is the spreadsheet itself coming from? Is it from another software package for example, or is it something you’ve made for use within your business?

3

Our business is to monitor utilities data from water, gas and electric meters. As clients add loggers and utility smart meters data sets to their exports files our software detects the new locations. At end of month these locations need to be invoiced. From our software CMS I can copy and paste the list of new locations into a spreadsheet very easily. Send to clients for confirmation regarding numbers / locations etc.
Next I need to manually cut and paste into Quickfile.
I prefer to send invoices with itemised lines rather than a bulk number invoice because the data logging contracts are for different time spans.
Such a solution would
be quicker
guarantee accuracy

4

There’s always a possibility of connecting your existing software to QuickFile, depending on the package itself and what’s generally available.

If you have a developer as part of your team, you may wish to point them towards our API documentation which could potentially be used to link them together.

But we’ll monitor this thread for interest and update it if there are any changes or plans

5

This sounds like a job for the API, ideally a module in the CMS (or in your own software) that creates invoices for you in QuickFile. If it’s any use to you, this is a quick adaptation of a script I use to create invoices via the API:

@Grab(group='org.codehaus.groovy.modules.http-builder', module='http-builder', version='0.7.1')
import groovyx.net.http.*
import static groovyx.net.http.ContentType.XML

@Grab('com.xlson.groovycsv:groovycsv:1.0')
import static com.xlson.groovycsv.CsvParser.parseCsv

println "Loading config"
def config = new ConfigSlurper().parse(new URL("file:api-config.groovy"))

def http = new RESTClient("https://www.quickfile.co.uk/WebServices/API/invoices.ashx")

def subno = System.currentTimeMillis()
// this closure ensures a unique SubmissionNumber each time it is used, and
// calculates the MD5 signature appropriately
def header = {
  def digest = java.security.MessageDigest.getInstance("MD5")
  digest.update((config.accountNumber + config.apiKey + subno).getBytes('UTF-8'))
  def md5Val = digest.digest().encodeHex()

  MessageType('Request')
  SubmissionNumber(subno++)
  Authentication {
    AccNumber(config.accountNumber)
    MD5Value(md5Val)
    ApplicationID(config.appId)
  }
}



def createInvoiceBody = {
  mkp.declareNamespace('':'http://www.QuickFile.co.uk') 
  mkp.declareNamespace('xsi':'http://www.w3.org/1999/XMLSchema-instance') 
  Invoice_Create('xsi:schemaLocation':'http://www.QuickFile.co.uk http://www.quickfile.co.uk/WebServices/API/Schemas/invoices/Invoice_Create.xsd') {
    Header(header)
    Body {
      InvoiceData {
        InvoiceType('INVOICE')
        ClientID(config.clientId)
        Currency('GBP')
        TermDays('30')
        Language('en')
        InvoiceDescription('Deliveries') // the "invoice name" in QuickFile
        SingleInvoiceData {
          IssueDate(String.format('%tF', new Date())) // today, as YYYY-MM-DD
        }
        InvoiceLines {
          ItemLines {
            new File(args[0]).withReader('UTF-8') { r ->
              parseCsv(r).each { line ->
                ItemLine {
                  ItemId('0')
                  ItemDescription(line.Description)
                  Tax1 {
                    TaxName('VAT')
                    TaxPercentage(line.VAT)
                  }
                  UnitCost(line.UnitCost)
                  Qty(line.QTY)
                }
              }
            }
          }
        }
      }
    }
  }
}

// Call QuickFile API
def createResponse = http.post(contentType:XML, requestContentType:XML, body:createInvoiceBody)
if(createResponse.data.Error.size()) {
  println "Error creating invoice"
  createResponse.data.Error.each {
    println it.text()
  }
  System.exit(1)
}

def newInvoiceId = createResponse.data.Body.InvoiceID.text()
def newInvoiceNumber = createResponse.data.Body.InvoiceNumber.text()

println "Created invoice ${newInvoiceNumber}"

It is written in Groovy and takes one command line parameter which is a CSV looking like this (it could have other columns, these four are the ones it cares about):

Description,UnitCost,QTY,VAT
Test item 1,1.35,7,20
Test item 2,10,4,0

It also expects a configuration file named api-config.groovy in the same directory with your API credentials:

apiKey = '...'
appId = '...'
accountNumber = '...'
clientId = '1234567'

The clientId is the QuickFile internal identifier for the client for which the invoice should be created (the NNN in the you.quickfile.co.uk/clients/detail?cID=NNN URL for the client detail page).

1 Like
6

Just to add to what @ian_roberts says, if you’re not a developer and don’t know of one, you can contact one who deals with QuickFile. You can fill out a contact form to contact them by going to Account Settings >> 3rd Party Integration >> API, and clicking the link at the bottom.