i have code request issues jira rest api , when have simple field i'm having troubles type object required, , when put object type requires string.. need parse json , put information excel... here's code:
public sub import_json_from_url(url jirajsonget) thisworkbook.sheets("rawdata").activate result = url.loadjson dim total long total = getlastrow() doinit dim keys() string keys = decode.getkeys(jsonobject) dim issues object set issues = decode.getobjectproperty(jsonobject, keys(4)) dim field, issue_project, issue_type, issue_status, issue_summary, issue_report, issue_created, issue_updated, issue_assignee, issue_priority, issue_resolution, issue_resolved, issue_time_spent, issue_time_estimated, issue_project_type object dim key() string, k_fields() string, k_project() string, k_issuetype() string, k_status() string, k_summary() string, k_report() string, k_created() string, k_updated() string, k_assignee() string, k_priority() string, k_resolution() string, k_resolved() string, k_timespent() string, k_timeestimated() string, k_projecttype() string dim issue variant, ki variant, kf variant, kproject variant, kissuetype variant, kstatus variant, ksummary variant, kreport variant, kcreated variant, kupdated variant, kassignee variant, kpriority variant, kresolution variant, kresolved variant, ktimespent variant, ktimeestimated variant dim project_name_issue string, project_key_issue string, key_issue string, issue_type_name string, issue_type_description string, status_name_issue string, summary_key string, report_key string, created string, updated string, assignee string, priority string, resolution string, resolved string, timespent string, timeestimated string, today string today = date thisworkbook.sheets("rawdata").range("a" & 1).value = "project" thisworkbook.sheets("rawdata").range("b" & 1).value = "key" thisworkbook.sheets("rawdata").range("c" & 1).value = "issues type" thisworkbook.sheets("rawdata").range("d" & 1).value = "status" thisworkbook.sheets("rawdata").range("e" & 1).value = "summary" thisworkbook.sheets("rawdata").range("f" & 1).value = "report" thisworkbook.sheets("rawdata").range("g" & 1).value = "created" thisworkbook.sheets("rawdata").range("h" & 1).value = "updated" thisworkbook.sheets("rawdata").range("i" & 1).value = "assignee" thisworkbook.sheets("rawdata").range("j" & 1).value = "priority" thisworkbook.sheets("rawdata").range("k" & 1).value = "resolution" thisworkbook.sheets("rawdata").range("l" & 1).value = "resolved" thisworkbook.sheets("rawdata").range("m" & 1).value = "time spent" thisworkbook.sheets("rawdata").range("n" & 1).value = "time estimated" thisworkbook.sheets("rawdata").range("o" & 1).value = "today" each issue in issues total = total + 1 key = decode.getkeys(issue) each ki in key if ki = "key" key_issue = decode.getproperty(issue, ki) thisworkbook.sheets("rawdata").range("b" & total).value = key_issue end if if ki = "fields" set field = decode.getobjectproperty(issue, ki) k_fields = decode.getkeys(field) each kf in k_fields if kf = "project" set issue_project = decode.getobjectproperty(field, kf) k_project = decode.getkeys(issue_project) each kproject in k_project if kproject = "name" project_name_issue = decode.getproperty(issue_project, kproject) thisworkbook.sheets("rawdata").range("a" & total).value = project_name_issue end if if kproject = "key" project_key_issue = decode.getproperty(issue_project, kproject) end if next end if if kf = "issuetype" set issue_type = decode.getobjectproperty(field, kf) k_issuetype = decode.getkeys(issue_type) each kissuetype in k_issuetype if kissuetype = "name" issue_type_name = decode.getproperty(issue_type, kissuetype) thisworkbook.sheets("rawdata").range("c" & total).value = issue_type_name end if if kissuetype = "description" issue_type_description = decode.getproperty(issue_type, kissuetype) end if next end if if kf = "status" set issue_status = decode.getobjectproperty(field, kf) k_status = decode.getkeys(issue_status) each kstatus in k_status if kstatus = "status" status_name_issue = decode.getproperty(issue_status, kstatus) thisworkbook.sheets("rawdata").range("d" & total).value = status_name_issue 'i=lastrow end if next end if if kf = "summary" issue_summary = decode.getproperty(field, kf) thisworkbook.sheets("rawdata").range("e" & total).value = summary_key end if if kf = "reporter" set issue_report = decode.getobjectproperty(field, kf) k_report = decode.getkeys(issue_report) each kreport in k_report if kreport = "displayname" report_key = decode.getproperty(issue_report, kreport) thisworkbook.sheets("rawdata").range("f" & total).value = report_key end if next end if if kf = "created" set issue_created = decode.getproperty(field, kf) thisworkbook.sheets("rawdata").range("g" & total).value = created end if if kf = "updated" set issue_updated = decode.getproperty(field, kf) thisworkbook.sheets("rawdata").range("h" & total).value = updated end if if kf = "assignee" set issue_assignee = decode.getobjectproperty(field, kf) k_assignee = decode.getkeys(issue_assignee) each kassignee in k_assignee if kassignee = "name" assignee = decode.getproperty(issue_assignee, kassignee) thisworkbook.sheets("rawdata").range("i" & total).value = assignee end if next end if if kf = "priority" set issue_priority = decode.getobjectproperty(field, kf) k_priority = decode.getkeys(issue_priority) each kpriority in k_priority if kpriority = "name" priority = decode.getproperty(issue_priority, kpriority) thisworkbook.sheets("rawdata").range("j" & total).value = priority end if next end if if kf = "resolution" set issue_resolution = decode.getobjectproperty(field, kf) k_resolution = decode.getkeys(issue_resolution) each kresolution in k_resolution if kresolution = "name" resolution = decode.getproperty(issue_resolution, kresolution) thisworkbook.sheets("rawdata").range("k" & total).value = resolution end if next end if if kf = "resolutiondate" set kresolved = decode.getproperty(field, kf) thisworkbook.sheets("rawdata").range("l" & total).value = kresolved end if if kf = "timespent" set ktimespent = decode.getproperty(field, kf) thisworkbook.sheets("rawdata").range("m" & total).value = ktimespent end if if kf = "timeestimated" set ktimeestimated = decode.getproperty(issue_time_estimated, kf) thisworkbook.sheets("rawdata").range("n" & total).value = ktimeestimated end if next end if next thisworkbook.sheets("rawdata").range("o" & total).value = today next end sub when have node in json whch sample fiel , not array i'm having troubles, in created, updated, timespent.. etc. idea??
Comments
Post a Comment