Python Forum
Python Forum

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Thread Modes
Exporting data from web (Trello)
Shafla
Silly Frenchman
Posts: 25
Threads: 9
Joined: Apr 2019
Reputation: 0
#1
May-11-2019, 07:37 PM (This post was last modified: May-11-2019, 07:41 PM by Yoriz.)
Hi
I am trying export some data from a web page and import it to an Exel sheet or Google Sheet. The Website is Trello which i use for project management. In Trello there is lists and in the lists there are cards. In the cards there are checklists. I manage the construction of apartments. so i assign a building to a list, an apartment to a card and in each card a generic checklist, eg..
Task 1 Walls up,
Task 2 Cables in walls,
Task 3 Walls closed,
Task 4 Walls Painted and so on..
Once a task is complete a member of the Trello board will tick one of these tasks as complete. I then need to open each card individually and physically export this information to an Exel Sheet. I am trying to automate this process.
Below is a JSON file of a sample Trello board i made. in lines 572-579 is the code for the first checklist item. I want to export this item and its status (complete or incomplete) to a cell of an Exel Sheet or a Google sheet.

// 20190511200807
// https://trello.com/b/bcCR15aF.json

{
  "id": "5cd716ab87210e88cb780993",
  "name": "Test Python",
  "desc": "",
  "descData": null,
  "closed": false,
  "idOrganization": null,
  "limits": {
    "attachments": {
      "perBoard": {
        "status": "ok",
        "disableAt": 36000,
        "warnAt": 32400
      },
      "perCard": {
        "status": "ok",
        "disableAt": 1000,
        "warnAt": 900
      }
    },
    "boards": {
      "totalMembersPerBoard": {
        "status": "ok",
        "disableAt": 1600,
        "warnAt": 1440
      }
    },
    "cards": {
      "openPerBoard": {
        "status": "ok",
        "disableAt": 5000,
        "warnAt": 4500
      },
      "openPerList": {
        "status": "ok",
        "disableAt": 5000,
        "warnAt": 4500
      },
      "totalPerBoard": {
        "status": "ok",
        "disableAt": 2000000,
        "warnAt": 1800000
      },
      "totalPerList": {
        "status": "ok",
        "disableAt": 1000000,
        "warnAt": 900000
      }
    },
    "checklists": {
      "perBoard": {
        "status": "ok",
        "disableAt": 2000000,
        "warnAt": 1800000
      },
      "perCard": {
        "status": "ok",
        "disableAt": 500,
        "warnAt": 450
      }
    },
    "checkItems": {
      "perChecklist": {
        "status": "ok",
        "disableAt": 200,
        "warnAt": 180
      }
    },
    "customFields": {
      "perBoard": {
        "status": "ok",
        "disableAt": 50,
        "warnAt": 45
      }
    },
    "customFieldOptions": {
      "perField": {
        "status": "ok",
        "disableAt": 50,
        "warnAt": 45
      }
    },
    "labels": {
      "perBoard": {
        "status": "ok",
        "disableAt": 1000,
        "warnAt": 900
      }
    },
    "lists": {
      "openPerBoard": {
        "status": "ok",
        "disableAt": 500,
        "warnAt": 450
      },
      "totalPerBoard": {
        "status": "ok",
        "disableAt": 3000,
        "warnAt": 2700
      }
    },
    "stickers": {
      "perCard": {
        "status": "ok",
        "disableAt": 70,
        "warnAt": 63
      }
    },
    "reactions": {
      "perAction": {
        "status": "ok",
        "disableAt": 1000,
        "warnAt": 900
      },
      "uniquePerAction": {
        "status": "ok",
        "disableAt": 17,
        "warnAt": 16
      }
    }
  },
  "pinned": false,
  "starred": false,
  "url": "https://trello.com/b/bcCR15aF/test-python",
  "prefs": {
    "permissionLevel": "private",
    "voting": "disabled",
    "comments": "members",
    "invitations": "members",
    "selfJoin": false,
    "cardCovers": true,
    "isTemplate": false,
    "cardAging": "regular",
    "calendarFeedEnabled": false,
    "background": "5c369a71115e4a500728b342",
    "backgroundImage": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/1365x2048/2592a2c00c59bd676f116229de21ed87/photo-1467217440414-bf182fc492d9",
    "backgroundImageScaled": [
      {
        "width": 67,
        "height": 100,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/67x100/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 128,
        "height": 192,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/128x192/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 320,
        "height": 480,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/320x480/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 640,
        "height": 960,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/640x960/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 683,
        "height": 1024,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/683x1024/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 853,
        "height": 1280,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/853x1280/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 1280,
        "height": 1920,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/1280x1920/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 1066,
        "height": 1600,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/1066x1600/dd283766c268d815d9ebfaa14d0af899/photo-1467217440414-bf182fc492d9.jpg"
      },
      {
        "width": 1365,
        "height": 2048,
        "url": "https://trello-backgrounds.s3.amazonaws.com/SharedBackground/1365x2048/2592a2c00c59bd676f116229de21ed87/photo-1467217440414-bf182fc492d9"
      }
    ],
    "backgroundTile": false,
    "backgroundBrightness": "light",
    "backgroundBottomColor": "#66645b",
    "backgroundTopColor": "#85797e",
    "canBePublic": true,
    "canBeEnterprise": true,
    "canBeOrg": true,
    "canBePrivate": true,
    "canInvite": true
  },
  "shortLink": "bcCR15aF",
  "subscribed": false,
  "labelNames": {
    "green": "",
    "yellow": "",
    "orange": "",
    "red": "",
    "purple": "",
    "blue": "",
    "sky": "",
    "lime": "",
    "pink": "",
    "black": ""
  },
  "powerUps": [
    
  ],
  "dateLastActivity": "2019-05-11T18:40:30.746Z",
  "dateLastView": "2019-05-11T19:08:07.505Z",
  "shortUrl": "https://trello.com/b/bcCR15aF",
  "idTags": [
    
  ],
  "datePluginDisable": null,
  "creationMethod": null,
  "ixUpdate": "28",
  "actions": [
    {
      "id": "5cd716f3dea0c166f7844d91",
      "idMemberCreator": "54d372bcf74d4723be02a89c",
      "data": {
        "board": {
          "shortLink": "bcCR15aF",
          "name": "Test Python",
          "id": "5cd716ab87210e88cb780993"
        },
        "checklist": {
          "name": "Checklist Items to play with ",
          "id": "5cd716f3dea0c166f7844d90"
        },
        "card": {
          "shortLink": "JOLfF8lA",
          "idShort": 1,
          "name": "First Card",
          "id": "5cd716d2c556ee88e239b3e3"
        }
      },
      "type": "addChecklistToCard",
      "date": "2019-05-11T18:39:47.428Z",
      "limits": {
        
      },
      "memberCreator": {
        "id": "54d372bcf74d4723be02a89c",
        "avatarHash": null,
        "avatarUrl": null,
        "fullName": "Shane Flanagan",
        "idMemberReferrer": null,
        "initials": "SF",
        "nonPublic": {
          
        },
        "nonPublicAvailable": false,
        "username": "shaneflanagan2"
      }
    },
    {
      "id": "5cd716d2c556ee88e239b3e4",
      "idMemberCreator": "54d372bcf74d4723be02a89c",
      "data": {
        "board": {
          "shortLink": "bcCR15aF",
          "name": "Test Python",
          "id": "5cd716ab87210e88cb780993"
        },
        "list": {
          "name": "My Number 1 Sample List",
          "id": "5cd716c53037eb644c58fb82"
        },
        "card": {
          "shortLink": "JOLfF8lA",
          "idShort": 1,
          "name": "First Card",
          "id": "5cd716d2c556ee88e239b3e3"
        }
      },
      "type": "createCard",
      "date": "2019-05-11T18:39:14.294Z",
      "limits": {
        
      },
      "memberCreator": {
        "id": "54d372bcf74d4723be02a89c",
        "avatarHash": null,
        "avatarUrl": null,
        "fullName": "Shane Flanagan",
        "idMemberReferrer": null,
        "initials": "SF",
        "nonPublic": {
          
        },
        "nonPublicAvailable": false,
        "username": "shaneflanagan2"
      }
    },
    {
      "id": "5cd716c53037eb644c58fb83",
      "idMemberCreator": "54d372bcf74d4723be02a89c",
      "data": {
        "board": {
          "shortLink": "bcCR15aF",
          "name": "Test Python",
          "id": "5cd716ab87210e88cb780993"
        },
        "list": {
          "name": "My Number 1 Sample List",
          "id": "5cd716c53037eb644c58fb82"
        }
      },
      "type": "createList",
      "date": "2019-05-11T18:39:01.718Z",
      "limits": {
        
      },
      "memberCreator": {
        "id": "54d372bcf74d4723be02a89c",
        "avatarHash": null,
        "avatarUrl": null,
        "fullName": "Shane Flanagan",
        "idMemberReferrer": null,
        "initials": "SF",
        "nonPublic": {
          
        },
        "nonPublicAvailable": false,
        "username": "shaneflanagan2"
      }
    },
    {
      "id": "5cd716ab87210e88cb780995",
      "idMemberCreator": "54d372bcf74d4723be02a89c",
      "data": {
        "board": {
          "shortLink": "bcCR15aF",
          "name": "Test Python",
          "id": "5cd716ab87210e88cb780993"
        }
      },
      "type": "createBoard",
      "date": "2019-05-11T18:38:35.833Z",
      "limits": {
        
      },
      "memberCreator": {
        "id": "54d372bcf74d4723be02a89c",
        "avatarHash": null,
        "avatarUrl": null,
        "fullName": "Shane Flanagan",
        "idMemberReferrer": null,
        "initials": "SF",
        "nonPublic": {
          
        },
        "nonPublicAvailable": false,
        "username": "shaneflanagan2"
      }
    }
  ],
  "cards": [
    {
      "id": "5cd716d2c556ee88e239b3e3",
      "address": null,
      "checkItemStates": null,
      "closed": false,
      "coordinates": null,
      "creationMethod": null,
      "dateLastActivity": "2019-05-11T18:40:30.746Z",
      "desc": "",
      "descData": null,
      "dueReminder": null,
      "idBoard": "5cd716ab87210e88cb780993",
      "idLabels": [
        
      ],
      "idList": "5cd716c53037eb644c58fb82",
      "idMembersVoted": [
        
      ],
      "idShort": 1,
      "idAttachmentCover": null,
      "limits": {
        "attachments": {
          "perCard": {
            "status": "ok",
            "disableAt": 1000,
            "warnAt": 900
          }
        },
        "checklists": {
          "perCard": {
            "status": "ok",
            "disableAt": 500,
            "warnAt": 450
          }
        },
        "stickers": {
          "perCard": {
            "status": "ok",
            "disableAt": 70,
            "warnAt": 63
          }
        }
      },
      "locationName": null,
      "manualCoverAttachment": false,
      "name": "First Card",
      "pos": 65535,
      "shortLink": "JOLfF8lA",
      "badges": {
        "attachmentsByType": {
          "trello": {
            "board": 0,
            "card": 0
          }
        },
        "location": false,
        "votes": 0,
        "viewingMemberVoted": false,
        "subscribed": false,
        "fogbugz": "",
        "checkItems": 3,
        "checkItemsChecked": 0,
        "comments": 0,
        "attachments": 0,
        "description": false,
        "due": null,
        "dueComplete": false
      },
      "dueComplete": false,
      "due": null,
      "email": "shaneflanagan+2jml9mjwmgnqycdh3fg+2sa8ibq0g0a577sw737+28awozdh0n@boards.trello.com",
      "idChecklists": [
        "5cd716f3dea0c166f7844d90"
      ],
      "idMembers": [
        
      ],
      "labels": [
        
      ],
      "shortUrl": "https://trello.com/c/JOLfF8lA",
      "subscribed": false,
      "url": "https://trello.com/c/JOLfF8lA/1-first-card",
      "attachments": [
        
      ],
      "pluginData": [
        
      ],
      "customFieldItems": [
        
      ]
    }
  ],
  "labels": [
    {
      "id": "5cd716ab91d0c2ddc5b20b34",
      "idBoard": "5cd716ab87210e88cb780993",
      "name": "",
      "color": "yellow"
    },
    {
      "id": "5cd716ab91d0c2ddc5b20b35",
      "idBoard": "5cd716ab87210e88cb780993",
      "name": "",
      "color": "green"
    },
    {
      "id": "5cd716ab91d0c2ddc5b20b36",
      "idBoard": "5cd716ab87210e88cb780993",
      "name": "",
      "color": "orange"
    },
    {
      "id": "5cd716ab91d0c2ddc5b20b38",
      "idBoard": "5cd716ab87210e88cb780993",
      "name": "",
      "color": "red"
    },
    {
      "id": "5cd716ab91d0c2ddc5b20b3e",
      "idBoard": "5cd716ab87210e88cb780993",
      "name": "",
      "color": "purple"
    },
    {
      "id": "5cd716ab91d0c2ddc5b20b3f",
      "idBoard": "5cd716ab87210e88cb780993",
      "name": "",
      "color": "blue"
    }
  ],
  "lists": [
    {
      "id": "5cd716c53037eb644c58fb82",
      "name": "My Number 1 Sample List",
      "closed": false,
      "idBoard": "5cd716ab87210e88cb780993",
      "pos": 65535,
      "subscribed": false,
      "softLimit": null,
      "limits": {
        "cards": {
          "openPerList": {
            "status": "ok",
            "disableAt": 5000,
            "warnAt": 4500
          },
          "totalPerList": {
            "status": "ok",
            "disableAt": 1000000,
            "warnAt": 900000
          }
        }
      },
      "creationMethod": null
    }
  ],
  "members": [
    {
      "id": "54d372bcf74d4723be02a89c",
      "avatarHash": null,
      "avatarUrl": null,
      "bio": "",
      "bioData": null,
      "confirmed": true,
      "fullName": "Shane Flanagan",
      "idEnterprise": null,
      "idEnterprisesDeactivated": [
        
      ],
      "idMemberReferrer": null,
      "idPremOrgsAdmin": [
        
      ],
      "initials": "SF",
      "memberType": "normal",
      "nonPublic": {
        
      },
      "nonPublicAvailable": false,
      "products": [
        37
      ],
      "url": "https://trello.com/shaneflanagan2",
      "username": "shaneflanagan2",
      "status": "disconnected"
    }
  ],
  "checklists": [
    {
      "id": "5cd716f3dea0c166f7844d90",
      "name": "Checklist Items to play with ",
      "idBoard": "5cd716ab87210e88cb780993",
      "idCard": "5cd716d2c556ee88e239b3e3",
      "pos": 16384,
      "limits": {
        "checkItems": {
          "perChecklist": {
            "status": "ok",
            "disableAt": 200,
            "warnAt": 180
          }
        }
      },
      "checkItems": [
        {
          "idChecklist": "5cd716f3dea0c166f7844d90",
          "state": "incomplete",
          "id": "5cd717024ec65679562e7eab",
          "name": "Taskio Number 1",
          "nameData": null,
          "pos": 16405
        },
        {
          "idChecklist": "5cd716f3dea0c166f7844d90",
          "state": "incomplete",
          "id": "5cd7170ae7189b28d5cc7659",
          "name": "Taskio Number 2",
          "nameData": null,
          "pos": 33624
        },
        {
          "idChecklist": "5cd716f3dea0c166f7844d90",
          "state": "incomplete",
          "id": "5cd7171a4e690b6639f0b36b",
          "name": "Taskio Number 3",
          "nameData": {
            "emoji": {
              
            }
          },
          "pos": 50031
        }
      ],
      "creationMethod": null
    }
  ],
  "customFields": [
    
  ],
  "memberships": [
    {
      "id": "5cd716ab87210e88cb780994",
      "idMember": "54d372bcf74d4723be02a89c",
      "memberType": "admin",
      "unconfirmed": false,
      "deactivated": false
    }
  ],
  "pluginData": [
    
  ]
}
Find
Reply
Larz60+
aetate et sapientia
Super Moderators
Posts: 12,137
Threads: 496
Joined: Sep 2016
Reputation: 452
#2
May-12-2019, 12:16 AM (This post was last modified: May-12-2019, 12:16 AM by Larz60+.)
** NOTE ** This code uses f-string so requires python 3.6 or newer (I use 3.7.3)
Here's something I got started you can play with
First, I saved your script as 'Shalfa.json', then delete the top three lines
and run this code (with file in same directory)
It's not correct yet, but started.
import json
import os

def read_json():
    data_dict = {}
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    with open('Shafla.json', 'r') as fp:
        data_dict = json.load(fp)
    return data_dict

def display_dict(dictname, level=0):
    indent = " " * (4 * level)
    for key, value in dictname.items():
        if isinstance(value, dict):
            print(f'\n{indent}{key}')
            level += 1
            display_dict(value, level)
        else:
            print(f'{indent}{key}: {value}')
        if level > 0:
            level -= 1

def main():
    mydict = read_json()
    allkeys = mydict.keys()

    checklistnode = mydict['checklists'][0]
    print(f'\nlist of available keys in checklists node: {checklistnode.keys()}\n\nnode contents:')

    print(f'\n=====================================================================')
    print(f'checklistnode:')
    print(f'\n=====================================================================')
    display_dict(checklistnode)

    checkitemsnode = checklistnode['checkItems']
    for n, item in enumerate(checkitemsnode):
        print(f'\n=====================================================================')
        print(f'checkitemsnode item {n}')
        print(f'\n=====================================================================')
        display_dict(item)
    
if __name__ == '__main__':
    main()
output:
Output:
list of available keys in checklists node: dict_keys(['id', 'name', 'idBoard', 'idCard', 'pos', 'limits', 'checkItems', 'creationMethod'])

node contents:

=====================================================================
checklistnode:

=====================================================================
id: 5cd716f3dea0c166f7844d90
name: Checklist Items to play with 
idBoard: 5cd716ab87210e88cb780993
idCard: 5cd716d2c556ee88e239b3e3
pos: 16384

limits

    checkItems

        perChecklist
            status: ok
            disableAt: 200
            warnAt: 180
checkItems: [{'idChecklist': '5cd716f3dea0c166f7844d90', 'state': 'incomplete', 'id': '5cd717024ec65679562e7eab', 'name': 'Taskio Number 1', 'nameData': None, 'pos': 16405}, {'idChecklist': '5cd716f3dea0c166f7844d90', 'state': 'incomplete', 'id': '5cd7170ae7189b28d5cc7659', 'name': 'Taskio Number 2', 'nameData': None, 'pos': 33624}, {'idChecklist': '5cd716f3dea0c166f7844d90', 'state': 'incomplete', 'id': '5cd7171a4e690b6639f0b36b', 'name': 'Taskio Number 3', 'nameData': {'emoji': {}}, 'pos': 50031}]
creationMethod: None

=====================================================================
checkitemsnode item 0

=====================================================================
idChecklist: 5cd716f3dea0c166f7844d90
state: incomplete
id: 5cd717024ec65679562e7eab
name: Taskio Number 1
nameData: None
pos: 16405

=====================================================================
checkitemsnode item 1

=====================================================================
idChecklist: 5cd716f3dea0c166f7844d90
state: incomplete
id: 5cd7170ae7189b28d5cc7659
name: Taskio Number 2
nameData: None
pos: 33624

=====================================================================
checkitemsnode item 2

=====================================================================
idChecklist: 5cd716f3dea0c166f7844d90
state: incomplete
id: 5cd7171a4e690b6639f0b36b
name: Taskio Number 3

nameData

    emoji
pos: 50031
Find
Reply
Shafla
Silly Frenchman
Posts: 25
Threads: 9
Joined: Apr 2019
Reputation: 0
#3
May-12-2019, 09:01 PM
Hi Larz

Thank you for this. I am a complete newby and i am still trying to get my head around some of the terminology so please forgive the next few questions.
1. When you say you have saved the script as Shafla.json, I have saved it and an IDLE file which will actually be Shafla.json.py is that correct??

2. In line 2 it says import os. What is os?

3. In line 6 there is an option to insert file. is this the exel file i intend to import the information to?

4. Would it be possible to add some comments to the code explaining each step? eg Line 8 
    with open('Shafla.json', 'r') as fp:
what is 'r' and fp?
in line 16 what is f'?

5. If i am trying to automate the process of exporting this information and importing it to an exel sheet would i have to export the json file every time or is there a better way?
Find
Reply
Larz60+
aetate et sapientia
Super Moderators
Posts: 12,137
Threads: 496
Joined: Sep 2016
Reputation: 452
#4
May-13-2019, 12:50 AM
Quote:1. When you say you have saved the script as Shafla.json, I have saved it and an IDLE file which will actually be Shafla.json.py is that correct??
No -- save it as yourfilename.json.
Json is a text file with a particular format.
so:
  • copy your listing from post 1 to a text file.
  • Delete the top three lines (now 1st line should begin with {
  • Save to a text file with .json suffix, call it whatever you want, but make sure suffix is .json

Quote:2. In line 2 it says import os. What is os?
os is a built-in python package. Miscellaneous operating system interfaces https://docs.python.org/3/library/os.html
the code os.chdir(os.path.abspath(os.path.dirname(__file__))) assures that when I try to open a file, I'm
looking for it in the script directory (where the program resides).

Quote:3. In line 6 there is an option to insert file. is this the exel file i intend to import the information to?
??? Don't know what you're looking at.

Quote:Would it be possible to add some comments to the code explaining each step? eg Line 8

Study the code, most of it is pretty basic, The DisplayDict method is a generic method that will display contents of any dictionary, It uses some techniques (includes recursion (calls itself)) that you shouldn't worry about until you you are more familiar with python. if you have a specific question, ask here and I will answer.

Quote:what is 'r' and fp?
On the open statement 'r' means read mode. fp is just a file handle I assign to the instance, this could have been fileptr or myfilename, or whatever your fancy. For me fp is short for file pointer.

Quote:in line 16 what is f'?
f stands for f-string it was implemented in python 3.6, it allows for abbreviated formatting of data, see: https://docs.python.org/3/whatsnew/3.6.h...w36-pep498
print(f'\n{indent}{key}') new format
print('\n{}{}'.format(indent, key) old format

Quote:5. If i am trying to automate the process of exporting this information and importing it to an excel sheet would i have to export the json file every time or is there a better way?
what is the original source. If not a spreadsheet, you are over complicating the process.
It's better to use the original json source.

FYI: Please do not send PM, It's best to ask all questions here, that way others can benifit.
You really need to take an elementary python tutorial,I'd suggest:
https://interactivepython.org/runestone/...index.html
or
https://www.python-course.eu/python3_course.php
Find
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Exporting excel data to website bdarragh00 1 3,852 Jun-15-2018, 08:57 PM
Last Post: micseydel

Forum Jump:

User Panel Messages

Log Out
My Profile
Pay your profile a visit
User Control Panel
Do some changes on your profile
My Messages
View private messages unread
Avatar
Change avatar
Signature
Change signature
Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020