Wekan Sandstorm cards to CSV using Python
Code originally by ertanalytics (Eric Thompson / AZero). Script has been used for daily exports of a board.
- On Wekan grain, get Webkey like this:
https://api-URL.SUBDOMAIN.sandcats.io#APIKEY
- Modity URL, SUBDOMAIN and APIKEY to this Bash script that exports board to file directly:
curl https://Bearer:APIKEY@api-URL.SUBDOMAIN.sandcats.io/api/boards/sandstorm/export?authToken=#APIKEY > wekanboard.json
For local Sandstorm install:
curl http://Bearer:APIKEY@api-URL.local.sandstorm.io:6080/api/boards/sandstorm/export?authToken=#APIKEY > wekanboard.json
cards-to-csv.py
#Sandstorm Wekan API Access Testing
##Does not seem to pull the redirected content
import requests
from requests.auth import HTTPBasicAuth
from bs4 import BeautifulSoup
from time import sleep
import os
import sys
import urllib
#All imports needed site navigation
import datetime
from time import sleep
import time
from selenium import webdriver
#drive.get('http://www.google.com/');
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException
from splinter import *
#driver = webdriver.Chrome()
##Data Handling
import pandas as pd
import json
from bson import json_util, ObjectId
from pandas.io.json import json_normalize
reload(sys)
sys.setdefaultencoding('utf-8')
#Export API Command
apiURLnoAuth = 'https://Bearer:APIKEY@api-URL.SUBDOMAIN.sandcats.io/api/boards/sandstorm/export?authToken=#APIKEY'
sleep(1) #Time in seconds
# Choose the browser (default is Firefox)
browser2 = Browser('chrome')
# Fill in the url
browser2.visit(apiURLnoAuth)
sleep(1) #Time in seconds
soup = BeautifulSoup(browser2.html,'html.parser')
browser2.quit()
script = soup.find('pre').children.next()
sanitized = json.loads(script)
dflabels = pd.DataFrame(json_normalize(sanitized, 'labels'))
dflists = pd.DataFrame(json_normalize(sanitized, 'lists'))
dfcards = pd.DataFrame(json_normalize(sanitized, 'cards'))
dfusers = pd.DataFrame(json_normalize(sanitized, 'users'))
normalized = json_normalize(sanitized)
df = pd.DataFrame(normalized)
dflists['createdAt'] = pd.to_datetime(dflists['createdAt'])
dflists['updatedAt'] = pd.to_datetime(dflists['updatedAt'])
dfcards['createdAt'] = pd.to_datetime(dfcards['createdAt'])
dfcards['dateLastActivity'] = pd.to_datetime(dfcards['dateLastActivity'])
dfcards['title']=dfcards['title'].str.replace('\n','')
dfcards.to_csv('//DESTINATION_FOLDER/dfcards.csv',sep='|')
dflists.to_csv('//DESTINATION_FOLDER/dflists.csv',sep='|')
dflabels.to_csv('//DESTINATION_FOLDER/dfboardsLabels.csv',sep='|')
dfusers.to_csv('//DESTINATION_FOLDER/dfusers.csv',sep='|')