Howto:Edit and upload contacts at the ContactsApp with Libre Office

Applies To

This information applies to

• Innovaphone PBX V13 with a running AppPlattform and a running ContactsApp-Instance.

Problem Details

Up from V13 there is with the ContactsApp a nice tool for serving contacts. Unfortunately there is no Contacts-Editor, but there are some customers outside, which have no LDAP-Source and which are not so fit in handling the UTF8-Export/Import. This project is a try to make it more easy for the customers, because they can edit the contacts and have only to click upload (if you prepared everything well).

System Requirements

• Innovaphone PBX V13 with a running AppPlattform and a running ContactsApp-Instance
• A Windows-PC that supports PowerShell (tested with Win10)
• workspace-folder with write-access on the PC with a real mounted drive (C:, D:...)
• the PowerShell-Script from Concept App Contacts saved as CSV-Import.ps1 and the CURL-Ressources in the workspace-folder
• LibreOffice (portable or installed is not important)
  • tested with LibreOffice V5.4
  • tested with OpenOffice V4.1
• The ODF-Document from this article

Setting up LibreOffice

• Depending on your security settings for executing macros you have to adjust this. Put your document-path to the trust-list (Tools -> Options -> LibreOffice -> Macro Security -> Trusted Sources).

Description of the ODS-file

Tables

The document contains 4 sheets. The 4th sheet is hidden and there is no need to show it. You can edit the name of the visible sheets, but don't rearange them - the macro needs them in this order.

PhoneBook

As you already assumed this is where you edit your contacts. You can customize row 1 and 3, but data has to start at cell A4 and keep the columns as they are. At hidden row 2 is the headline as used in export for debugging reasons.

Settings

Here is where you set your environment. For starting the PowerShell-Script there is a few information needed. This is the address (name or IP) of your App-Plattform. Then there is needed the put-user and the password, which you can set in the settings of the ContacsApp at the PBX-Manager. Additional there is the PBX-Domain needed, on which your instance is running. If you upload a file, the ContacsApp saves the filename and if you upload again you have to use the same filename to update your data and prevent a double data. Here is a problem at many customers, and so we write the destination name also in the settings to reduce the trouble here. At column A you will find the descriptions of the setting values and a notice on every cell which describes the value with a few words. At column B you have to enter the values and you will find a notice with a short German description.

ImportData

This table will be filled by the Upload-Macro and exported. Off course this can also be hidden, but not with the latest version of the macro. If you can improve this feel so free and do it and be so friendly and let us all know your improvements. Here it is important, that the row 1 is not modified, because this is not touched by the macro. So to make it a little safer it's hidden and you know, you can improve this too.

Hilfsdaten

This is the hidden sheet and you see this will be created in German. You don't need this sheet, so there was no need to translate it after creation. It calculates different valus for the macro, because it was the lazy way. Keep it hidden, it looks a little bit chaotic and don't modify it.

Macro

Most parts of the macro are recorded, so it's not optimized to a clean programming style. But you know - you can improve it. The macro jumps to cell A4 of the Phone-Book-sheet, marks all data and copy it to the clipboard. Then it jumps to the sheet Importdata and insert the clipboard-content, with the starting point A2. After this sheet will be saved at the workspace with the name upload.utf8. This will be done by the 'Save As' dialogue. To rescue some ugly things which are done until now, the clipboard will be filled with an empty cell and the document will be saved at its original name as ODS-file. Now the macro creates a cmd-file named uploader.cmd at the workspace. It writes some DOS-commands. The correct drive will be addressed and then the script jumps to the workspace-folder and starts the PowerShell-Script CSV-Import.ps1. At the end the script is deleting the import.utf8 and itself to prevent files with the credentials and contacts at the folder. The output of CSV-Import.ps1 will be saved in a file called upload.log.

Debugging

There are some ways to find a problem on this solution. At first have a look at the upload.log which is located in the workspace. Then you can stop the autorun of the script. Call the Macro-Editor in Libre Office Calc and edit the Basic-Macros. You will find the macro under document-makros in Module Innovaphone. It is simple named Main. Now you search for the command 'shell (MakroFile,4)' and make it to a comment by writing rem left from the command. After running the macro now you see the data-file upload.utf8 and the script-file uploader.cmd in the workspace and you can analyse both files and run uploader.cmd. You will find some other commented lines (rem print) at the macro, you can uncomment this to get some messages by running the macro.

Version History

V1.0

The first try.

V1.1

• Improve the handling of the UTF8-file (removed unneeded steps)
• Fixing a bug with wrong saving after user saved ODS-file under new name

Known Problems

• By running the Macro you can get a warning that only the current sheet is saved. Don't worry about this, the macro is fixing it one step later.
• By running the upload you lose the content of the clipboard on your PC.
• Because the password is written clear at the settings there is a little security risk. Maybe in the next version there comes a password-request if the value is empty, but you know - you can improve it too.
• There is no logical check for the settings, it is up to you to check everything.
• Naturally, because of the use of Windows-scripts it will only work on Windows.
• Of course this solution comes without any warranty or support. If you need help ask the Community.

Download

File:Contacts-App-Importer.zip