Using DataNitro, how do I print a python loop to excel, making sure that after 23 rows are printed, printing moves to the adjacent column?

Question

I am using grequest to pull json data from multiple urls. With the help of another user here, I was able to get python to print the results from each url in succession. Now, I want to print those results to excel using DataNitro. Here is the code I have now.

import grequests
import json


urls = [
    'https://bittrex.com/api/v1.1/public/getorderbook?market=BTC-1ST&type=both&depth=50',
    'https://bittrex.com/api/v1.1/public/getorderbook?market=BTC-AMP&type=both&depth=50',
    'https://bittrex.com/api/v1.1/public/getorderbook?market=BTC-ARDR&type=both&depth=50',
]

requests = (grequests.get(u) for u in urls)
responses = grequests.map(requests)

for response in responses:
    Cell("A1").value = "Buy"
    Cell("A2").value = (response.json()['result']['buy'][0]['Quantity'])
    Cell("A3").value = (response.json()['result']['buy'][0]['Rate'])
    Cell("A4").value = (response.json()['result']['buy'][1]['Quantity'])
    Cell("A5").value = (response.json()['result']['buy'][1]['Rate'])
    Cell("A6").value = (response.json()['result']['buy'][2]['Quantity'])
    Cell("A7").value = (response.json()['result']['buy'][2]['Rate'])
    Cell("A8").value = (response.json()['result']['buy'][3]['Quantity'])
    Cell("A9").value = (response.json()['result']['buy'][3]['Rate'])
    Cell("A10").value = (response.json()['result']['buy'][4]['Quantity'])
    Cell("A11").value = (response.json()['result']['buy'][4]['Rate'])
    Cell("A12").value = "Sell"
    Cell("A13").value = (response.json()['result']['sell'][0]['Quantity'])
    Cell("A14").value = (response.json()['result']['sell'][0]['Rate'])
    Cell("A15").value = (response.json()['result']['sell'][1]['Quantity'])
    Cell("A16").value = (response.json()['result']['sell'][1]['Rate'])
    Cell("A17").value = (response.json()['result']['sell'][2]['Quantity'])
    Cell("A18").value = (response.json()['result']['sell'][2]['Rate'])
    Cell("A19").value = (response.json()['result']['sell'][3]['Quantity'])
    Cell("A20").value = (response.json()['result']['sell'][3]['Rate'])
    Cell("A21").value = (response.json()['result']['sell'][4]['Quantity'])
    Cell("A22").value = (response.json()['result']['sell'][4]['Rate'])
    Cell("A23").value = "----"

This works just fine, but only if I comment out all but one url, otherwise the results from the first url are overwritten by the results from the second url, an expected result. However, this is not what I want. In the end, I want the results from the first url to print in cells A1:A23, the results from the second to print in cells B1:B23, and the third's to print in cells C1:C23.

Here is what I have tried:

for response in responses:
    #print("Buy")
    Quantity = [response.json()['result']['buy'][0]['Quantity'],
        response.json()['result']['buy'][1]['Quantity'],
        response.json()['result']['buy'][2]['Quantity'],
        response.json()['result']['buy'][3]['Quantity'],
        response.json()['result']['buy'][4]['Quantity']
]
    Cell("A1:E5").vertical = Quantity

(Note: I am only trying the Quantity here).

This does not work because it only prints the first url's results in column A, and it does not move on to column B to print subsequent results. I tried switching Cell("A1:E5").vertical = Quantity with Cell("A1:E5").vertical_range = Quantity, but python returns the following error:

    Cell("A1:E5").vertical_range = Quantity
  File "27\basic_io.py", line 379, in __setattr__
AttributeError: can't set attribute

I was thinking about doing something with my original set up (i.e., using the Cell("A2").value = (response.json()['result']['buy'][0]['Quantity']) type of code), adding something like i = 0, and then writing the last print line to read something like Cell(23,(1+i)).value = "----" but I could not figure out (mentally) how to make that work. With the Cell(23,(1+i)).value = "----" line, it would just print "----" in cell B1, and then it overwrites all the results in column A again, which, again, makes sense when you see what the code is actually doing.

I am at a loss, but I feel like the answer is relatively simple, and I am just missing it. I went through the 9 pages of results on this site that mention DataNitro and https://datanitro.com/docs/: I could not find an answer. I would appreciate any help!

EDIT: I realized that it does not matter that it prints 5 and then moves to the adjacent column. All I need it to do is print all the results to excel without overwriting itself: that can be all results show up in one column, or all results show up in one row.

I have since tried

CellRange((1,1),(1,2)).value = [response.json()['result']['buy'][0]['Quantity'],
response.json()['result']['buy'][0]['Rate']

But I am running into the same problem, it just overwrites the data from the first url. Thanks in advance.

Answer 1

Comment: Issue(3) is resolved when the entire worksheet is cleared of its data prior to rerunning the script

Rerunning the Script will always Start with row = 1. If you want to append Data to existing Sheet set row = MAX ROW OF OLD DATA +1.

---

Comment: How would this script change with different JSON structures. Compare bittrex.com/api/v1.1/public/… with api.livecoin.net/exchange/order_book?currencyPair=ABN/…

The First is a list of dict, the Second is a list of list. Use [0, 1] as keys, for example:

# Usage for list
row += json_to_cell('Buy', response.json()['result']['buy'], row, [0, 1])

---

Comment: some ... return less than 5 data entries, or sometimes, one URL will not work at all.

Generalized Approach:

def json_to_cell(title, _json, start_row, keys):
    values = len(_json)
    if values:
        CellRange((start_row, 1), (start_row, 1)).value = [title]
        start_row += 1

        for row, key in enumerate(keys, start_row):
            rowData = []
            for c in range(0, values):
                rowData.append(_json[c][key])

            CellRange((row, 1), (row, values)).value = rowData
        return len(keys) + 1
    else:
        # Write some Info NO VALUES
        return 0

keys = ['Quantity', 'Rate']
row = 1
for response in responses:
    row += json_to_cell('Buy', response.json()['result']['buy'], row, keys)
    row += json_to_cell('Sell', response.json()['result']['sell'], row, keys)

    CellRange((row, 1), (row, 1)).value = ['----']
    row += 1

---

Comment: IndexError: CellRange set to object of wrong lengt

Both, CellRange and len(rowData), should be 5.
Add a print(..., see below, and show one Output.

Use the following:

for row, response in enumerate(responses, 1):
    rowData = []
    for c in range(0, 5):
        rowData.append(response.json()['result']['buy'][c]['Quantity'])

    print( rowData)
    CellRange((row, 1),(row, 5)).value = rowData