Importing Data from Google Sheets

JordanLJohnson

Sometimes data exists in Google Sheets that needs to be brought in to FlexSim. There are multiple ways to do this, discussed in this article.

Copy and Paste

This is the easiest method to get data from Google Sheets into FlexSim. Here's how it works:

Open the desired sheet in your browser
Click the top-left corner to select everything.
Copy the data (use ctrl-C)
Open FlexSim
Create a Global Table if you haven't already
Ensure the number of rows and columns in the Global Table is large enough to hold the pasted data.
Click on the column header for the first row in the Global Table.
Paste the data (use ctrl-V)

Pros: Quick, easy

Cons: Need to resize the global table correctly beforehand, repeat entire process if data changes.

Export/Import via CSV

This is also any easy method to get data. Here are the steps:

Download your sheet as a csv file.
In FlexSim, use the importtable() command to dump the csv into the global table. For example:
```
importtable(Table("GlobalTable1"), "data.csv", 1)
```

You could add this code to your model's OnReset trigger if desired.

Pros: Quick, table sized to csv data automatically

Cons: Repeat downloading csv if the data changes.

Export/Import via XLSX

You can also download a google spreadsheet as an Excel file. Then you can use the Excel importer as normal.

Pros: Quick, table sized to data automatically, many options for configuring

Cons: Repeat downloading xlsx file if the data changes

Import via Python

This method is more advanced and requires some configuration for the model and your Google account. Once complete, however, changes can be pulled in automatically without any manual steps.

Follow the Sheets quickstart for python found here: https://developers.google.com/sheets/api/quickstart/python
Following this guide walk you through creating a Google Cloud Project and creating credentials for that project. In addition, consider using this modified python file instead. This file creates a get_values method that the model can call, and that method is also called from main(), so it's easy to test in a python debugger:

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

# If modifying these scopes, delete the file token.json.
SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = "----- add your sheet's ID here -------------"
SAMPLE_RANGE_NAME = "A1:B"


def get_values():
  """Shows basic usage of the Sheets API.
  Prints values from a sample spreadsheet.
  """
  creds = None
  # The file token.json stores the user's access and refresh tokens, and is
  # created automatically when the authorization flow completes for the first
  # time.
  if os.path.exists("token.json"):
    creds = Credentials.from_authorized_user_file("token.json", SCOPES)
  # If there are no (valid) credentials available, let the user log in.
  if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
      creds.refresh(Request())
    else:
      flow = InstalledAppFlow.from_client_secrets_file(
          "credentials.json", SCOPES
      )
      creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open("token.json", "w") as token:
      token.write(creds.to_json())

  try:
    service = build("sheets", "v4", credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result = (
        sheet.values()
        .get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME, valueRenderOption="UNFORMATTED_VALUE")
        .execute()
    )
    values = result.get("values", [])
    return values
  except HttpError as err:
    return []
  
def main():
    values = get_values()
    if not values:
      print("No data found.")
      return

    for row in values:
      print(row)

if __name__ == "__main__":
  main()

Save the above script next to your model.
Create a user command in your model.
Format the user command for python and enter the file name and method name. It might look something like this:
```
/**external python: */ /**/"sheets"/**/
/** \nfunction name:*/ /**/"get_values"/**/
```
The return type of the command should be var which means any Variant type.

Use code like the following to clone the data to a global table:

Array values = getValues(); // call the user command.

Array colHeaders = values.shift();
for (int i = 1; i <= values.length; i++) {
Array row = values;
row[0] = nullvar;
}
Table(values).cloneTo(Table("GlobalTable1"));

Add the above code to a reset trigger.

Pros: automatic once complete, easy to keep data up-to-date

Cons: requires complicated setup, some python coding. The script could be adjusted to download additional ranges, and then return all data at once, but that requires some code ability.

Import via HTTPS

Google recommends you use a client library to access its APIs. However, it is entirely possible to use HTTPS requests instead. This could all be done from FlexScript, with no additional installations required.

Pros: done all from FlexScript, no extra installs

Cons: very technical

Conclusion

There are several ways to extract data from Google Sheets into FlexSim. Each has pros and cons. Choose the one that best fits your circumstances. Good luck!

Community