Connect excel to snipe-IT DB

[Vodkius]

Server (please complete the following information):

• Snipe-IT Version v3.6.4 build 12 (gf7e4fca)
• OS: ubuntu
• Web Server: apache
• PHP Version -

Is your feature request related to a problem? Please describe.
I'd like to connect to snipe-it DB from excel and to be able to pull requested information in Excel.

Describe the solution you'd like
Be able to connect to snipe-it DB and pull requested information such as name,assets etc etc.

[bricelabelle]

We accomplish this by using Google Sheets + Google Script + the Snipe API. We create a Google sheet, open the script editor from the Tools menu, and then create a script that makes calls to the Snipe API and then filters the response for the data we want and writes it to the sheet. I've never used Excel to send web requests and parse data, but my guess is you could accomplish the same thing. If you are interested in how we do it in Google, I'm happy to help.

[Vodkius]

We accomplish this by using Google Sheets + Google Script + the Snipe API. We create a Google sheet, open the script editor from the Tools menu, and then create a script that makes calls to the Snipe API and then filters the response for the data we want and writes it to the sheet. I've never used Excel to send web requests and parse data, but my guess is you could accomplish the same thing. If you are interested in how we do it in Google, I'm happy to help.

Hi,

Thank you for your response. It would be amazingly helpful if you could provide information how to do it in Google! 👍

[bricelabelle]

Here's an example of one that we use to sort assets by department. It takes a list of department names from column 1 of the sheet, and searches all the users within each department and spits out a list of assets with their url to a tab with that department name.

1. Create a new Google sheet.
2. Open script editor and paste in the code from below.
3. At the top, enter your Snipe-it server URL and API key.
   Your API key can be created by choosing YourName > Manage API keys from the top right menu in Snipe.
4. In column 1, enter department names each in their own box.
5. Create a new tab for each department that matches the name.
6. Quit the sheet and relaunch. You should see a new menu option for Run Script.
7. Select the Department names from column 1 and run the script. It will write asset urls to the appropriate tabs based on user's departments and the assets assigned to them.

The code is basically JavaScript but is specific to Google for how it accesses and writes to a sheet and makes web requests. If you are familiar with JavaScript, you should be able to manipulate the functions to make calls to the API for information on Hardware, Licenses, and anything else accessible by the API (instead of users from my example). The documentation I linked before is helpful in making the requests to Snipe.

It might be overwhelming to look at the script all at once. It's essentially just a bunch of smaller functions that call Snipe, parse the response as JSON, then use a for loop to loop through the response and pull out the relevant data.

Hopefully this gets you on the right track.

Get Assets By Department.txt

[snipe]

This is great info - mind if we add it to the docs?

[bricelabelle]

Go for it! Unless I made a mistake, I believe I removed anything specific to my organization. I also have a bulk checkout/checkin/edit sheet that many might be interested in. Let me know if I should share that too.

[snipe]

Thanks again so much! We've added this to the docs! https://snipe-it.readme.io/v4.6.13/docs/connecting-to-google-docs

Connecting to Google Docs

Connect to the Snipe-IT API from Google Docs

[rodrigobuttow]

Does anyone know how to connect Google Sheet to Snipe IT API?

[bricelabelle]

@rodrigobuttow I'm not sure what you want to do, but at my last job we had a lot of connections to Snipe from Google. If you follow the instructions to set this up, it should give you an idea of how the connection works, and you can customize the functions to your own uses. https://github.com/bricelabelle/snipe-it-bulkedit

GitHub

GitHub - bricelabelle/snipe-it-bulkedit: Google Script files to use Google Sheets as a bulk checkout/checkin/edit tool for Snipe-it.

Google Script files to use Google Sheets as a bulk checkout/checkin/edit tool for Snipe-it. - GitHub - bricelabelle/snipe-it-bulkedit: Google Script files to use Google Sheets as a bulk checkout/ch...

[rodrigobuttow]

@bricelabelle Thank you very much, what I would like is to summarize what you did in the part of synchronizing the locations, but I couldn't make your script work, it didn't pull the locales.

What could I have done wrong?

[rodrigobuttow]

Hi @bricelabelle I managed to import the locations, but they were all in a single cell. How do I put each location on a line?

Thanks!