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.
This is the easiest method to get data from Google Sheets into FlexSim. Here's how it works:
Pros: Quick, easy
Cons: Need to resize the global table correctly beforehand, repeat entire process if data changes.
This is also any easy method to get data. Here are the steps:
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.
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
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.
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()
/**external python: */ /**/"sheets"/**/ /** \nfunction name:*/ /**/"get_values"/**/The return type of the command should be var which means any Variant type.
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"));
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.
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
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!