• State Resolved
  • Locked Locked
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 3 subscribers
  • Views 1018 views
  • Users 0 members are here
Support feedback
Options
Options
Related

This thread has been locked.

TI-API: Python: inventory search with for-in-loop with data from another (google sheet) API

David Kao
Prodigy 75 points
Part Number: TI-API


HI TI support,

I was trying to use for_in_loop to search inventory for a list of components in my google sheet.

That is:
data = result.get('values', [ ])
for CPlist in data:
      ....

and it retuned that I am using the first available part-number in google sheet to search, which is ['OPA2348AIDCNT']

When debugging, line 63 returns a warning: Unexpected HTTP status code 404.
However, same code in line 63 works pefectly with assigned part number 'OPA2348AIDCNT'

I assume that is because the extra Brackets and Apostrophes " [' '] " caused this problem but I don't know how to fix it since the value is from another (Google sheet) API.

from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2 import service_account

from TI_API_Suite import TI_Orders, TI_Inventory
from API_Accessor import API_Accessor

# GOOGLE SHEET: If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = '/Users/MingC_MBP/Desktop/工作/API projects/GoogleAPI/dkaotest1key.json'

creds = None
creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# GOOGLE SHEET:  The ID of Google Sheet.
DOCUMENT_ID = '---masked---'
service = build('sheets', 'v4', credentials=creds)

# GOOGLE SHEET:  --- Call the Sheets API ; get values in google sheet
# values = result.get('values', [])
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=DOCUMENT_ID,
                            range="Components RT-View!K2:K").execute()
#print(result)

data = result.get('values',[])

for CPlist in data:

        # TI API: --- use google sheet result as entry to TI API and search for Inv ---
        # TI API: Store user credentials and server URL address.
        client_id = "---masked---"
        client_secret = "masked---"
        server = "https://transact.ti.com"
        verify_ssl = True

        # TI API: Confirm to user.
        print("\nLogging into {} with ID {}.".format(server, client_id))
        api = API_Accessor(server=server, client_id=client_id, client_secret=client_secret, verify=verify_ssl)

        # TI API: Print output.
        print("Access token {} retrieved!".format(api.token))
        print("Expires on {}.".format(api.expiration.strftime("%m/%d/%Y at %H:%M:%S")))

        # TI API: Confirm to user.
        print("\nLogging into Inventory & Pricing API {} with ID {} and secret {}.".format(server, client_id, client_secret))
        catalog = TI_Inventory(server=server, client_id=client_id, client_secret=client_secret, verify=verify_ssl)


        # TI API: Step 1: Get inventory of the OPN below.
        opn  = CPlist
        print("\nFirst, we are using the inventory API to get how many items of OPN {} there are.".format(opn))
        response = catalog.get_product_info(opn=opn, verify=verify_ssl)


        # TI API: Step 2: View server response.
        print("Response: There are {} of this part available.".format(response.json()['quantity']))

  • 0
    TI__Intellectual 1276 points

    David:

    Thanks for reaching out to API Support. 

    Let us look into it and get back to you by Wed. 

    ~Samip

  • +1
    TI__Prodigy 250 points

    Hello David!

    The opn object is printing as ['OPA2348AIDCNT'] because it is not a string, but a list. The Google Sheets API documentation indicates that each element in data is a list object representing a row, not a singular cell given as a string, even if the row is only one cell wide. Therefore you may want to change line 57 to be:

    opn = CPlist[0]

    This will access the first (and only) element in the list, which is the OPN you wanted to retrieve from the spreadsheet. Here is a link to the Python quickstart for the Google Sheets API.

    I would also like to offer some advice on using the TI store API wrapper classes, since there are certain issues which may cause problems over time.

    1. API_Accessor is unnecessary here. An object of this class is created and used internally by the TI_Inventory object. This means that if you are using a specific wrapper module for a TI store API, you will not have to use API_Accessor. Notice that you are signing into the API suite twice, and that the api object is unused after being assigned. You could simply remove lines 43-49.
    2. You are logging into the inventory API for each call you make, because you are doing so inside the for-loop. Instead of doing this, I would recommend logging in outside of the for-loop so that you are doing it once per run. The relevant lines are 36-41 and 51-53 (since 43-49 should be removed).

    Thank you, and Happy Holidays! Slight smile

    Marcia

  • 0 in reply to Marcia Birchall-Roman
    Prodigy 75 points

    Hi Marcia,

    You made that work!! Thank you so much for the solution and professional advices. Here I have new questions popped up. 

    Python can now execute for_in_loop functions and get inventory info for a list of components. (line: 45-52)
    However, the list in my google sheet also includes Part Numbers from different manufacturers, and the loop will be terminated once it hits non-TI parts. Can we omit non-TI parts and record them as null or zero?

    In addition, I was trying to record the TI-API response, the inventory, on my google sheet but failed. (line: 55-57)
    I think my way of keeping values from TI-API was incorrect, could you give me some advice?

    from __future__ import print_function
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2 import service_account

from TI_API_Suite import TI_Orders, TI_Inventory

# GOOGLE SHEET: If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = '/Users/MingC_MBP/Desktop/工作/API projects/GoogleAPI/dkaotest1key.json'

creds = None
creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# GOOGLE SHEET:  The ID of Google Sheet.
DOCUMENT_ID = '---masked---'
service = build('sheets', 'v4', credentials=creds)

# GOOGLE SHEET:  --- Call the Sheets API ; get values in google sheet
# values = result.get('values', [])
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=DOCUMENT_ID,
                            range="Components RT-View!K2:K").execute()
#print(result)
data = result.get('values',)
#out=data.replace('[','').replace(']','')

# TI API: --- use google sheet result as entry to TI API and search for Inv ---
# TI API: Store user credentials and server URL address.
client_id = "---masked---"
client_secret = "---masked---"
server = "https://transact.ti.com"
verify_ssl = True

# TI API: Confirm to user.
print("\nLogging into Inventory & Pricing API {} with ID {} and secret {}.".format(server, client_id, client_secret))
catalog = TI_Inventory(server=server, client_id=client_id, client_secret=client_secret, verify=verify_ssl)


for CPlist in data:
        # TI API: Step 1: Get inventory of the OPN below.
        opn  = CPlist[0]
        print("\nFirst, we are using the inventory API to get how many items of OPN {} there are.".format(opn))
        response = catalog.get_product_info(opn=opn, verify=verify_ssl)

        # TI API: Step 2: View server response.
        print("Response: There are {} of this part available.".format(response.json()['quantity']))

# GOOGLE SHEET: --- write TI API "result_x" into google sheet--
request = sheet.values().update(spreadsheetId=DOCUMENT_ID, 
                                range="Components RT-View!L2:L", valueInputOption="USER_ENTERED", body={"values":response.json()['quantity']}).execute()
print(request)


    Thank you very much and Happy new year.
    David

  • 0 in reply to David Kao
    TI__Prodigy 250 points

    Hi David,

    If the manufacturer of the part is indicated on the spreadsheet, you could retrieve those rows to determine if the given part is TI or non-TI. Otherwise, there is nothing stopping you from setting the value of those rows to null or empty. You just need logic to ensure that whatever you pass into the API is a valid TI part number.

    For lines 55-57, I'm unfamiliar with Google's API client, but it seems like in line 49 you are updating the response object each time you retrieve information for a part. This means you are not retaining data for the previous parts, but only for the last part encountered in the sequence. You could create and continually append a list of quantities in the order of the parts encountered. It also seems like in line 56, you are passing a singular quantity and not a sequence; when you resolve line 49, make sure to pass the entire sequence of quantities rather than just a single one.

    Sincerely,
    Marcia

**Attention** This is a public forum