Working with Google Sheets

You can read/write data from/to Google Sheets using your favorite modeling language!

Supported languages and solving methods

Google Sheets integration is available for all of our paying users. However, it is required that the language/solving method adopted by the user supports either .csv or .xlsx files:

• NEOS — Not supported: unfortunately, NEOS does not accept .csv or .xlsx files.
• GLPSOL — Supported: users can read and write .csv files.
• Opt-server — Depends on the language being used:
  • AMPL — Supported: users can read and write .csv and .xlsx files.
  • GAMS — Supported: users can read and write .csv and .xlsx files.
  • MOSEL — Supported: users can read and write .csv and .xlsx files.
  • OPL — Partial support: it is technically possible to read .csv files, but not easily.

How it works

The integration between PIFOP and Google Sheets works through the syncronization between a Google Sheets file and a PIFOP's copy of that file.

When you import a Google Sheets into a PIFOP project, you create a file in your project that is linked to the Google Sheets file. Whenever you read some data from that PIFOP file, you are essentially reading the data from its associated Google Sheets file, and whenever you write some data to that PIFOP file, you are essentially writing the data to its associated Google Sheets file.

A PIFOP's copy of a Google Sheets file can be in one of two formats: CSV — extension .csv — or XLSX — extension .xlsx.

Enable Google Sheets integration

After upgrading your account, go to Account > Integrations and click on the Google Drive button. Follow the steps to give PIFOP permission to read and write files in your Google Drive.

Import or create a Google Sheet

STEP 1. Open or create a PIFOP project.

STEP 2. In the left menu, click on to Import or Create a spreadsheet

STEP 3. Name PIFOP's copy of the Google Sheet according to the type of file data you want to work with:

• .csv — Give it the same name of the worksheet that you want to import, adding the extension .csv. The name of the Google Sheet worksheet itself does not need to end with .csv, only PIFOP's copy of it. A Google Sheet file can contain multiple worksheets, but a .csv file can only reference a single worksheet. PIFOP uses the file name to detect which worksheet the file should be linked to. If you want to import multiple worksheets within a Google Sheets file, repeat the two previous steps for each worksheet.
• .xlsx — Give it any name ending with .xlsx.

Read and write Google Sheets as .csv

The above worksheets data.csv and result.csv may or may not be in the same workbook. See above how to import multiple worksheets in the same Google Sheet workbook.

See below how you can read/write the above worksheets in different languages/solvers, assuming that you have already imported them into your PIFOP project.

AMPL — official docs

Read: # load the CSV driver (do it only once) load "amplcsv.dll"; # declare input table table data_csv IN "amplcsv" "data.csv": PRODUCTS <- [PRODUCTS], rate, profit, market; # read table read table data_csv; Write: # load the CSV driver (do it only once) load "amplcsv.dll"; # declare output table table result_csv OUT "amplcsv" "result.csv": [PRODUCTS], Make; # write table write table result_csv;

GAMS — official docs

Read: * Declare parameter to hold the CSV data Parameter data_csv; * Convert 'data.csv' to 'data.gdx' $call csv2gdx data.csv id=data_csv index=1 values=2..lastCol useHeader=y * Load 'data_csv' and 'PRODUCTS' from the GDX file $gdxIn data.gdx $load data_csv $load PRODUCTS = dim1 * Get rate, profit and market from data_csv rate(PRODUCTS) = data_csv(PRODUCTS, 'rate'); profit(PRODUCTS) = data_csv(PRODUCTS, 'profit'); market(PRODUCTS) = data_csv(PRODUCTS, 'market'); Write: * Write Make to GDX execute_unload 'result.gdx', Make; * "Convert" 'result.gdx' to 'result.csv' execute "gdxdump result.gdx format=csv symb=Make header=PRODUCTS,Make output=result.csv";

GMPL (GLPSOL) — official docs

Read: table data_csv IN "CSV" "data.csv": PRODUCTS <- [PRODUCTS], rate, profit, market; Write: table result_csv{p in PRODUCTS} OUT "CSV" "result.csv": p ~ PRODUCTS, Make[p];

Read and write Google Sheets as .xlsx

See below how you can read/write the above worksheets in different languages/solvers, assuming that you have already imported them into your PIFOP project.

When you write to a .xlsx linked to a Google Sheet, the PIFOP's file overwrites the Google Sheets file completely, which causes the Google Sheet to be fully refreshed. If you find that inconvenient, consider the .csv approach to write your results, which does not cause a refresh.

AMPL — official docs

Read: # load the XLSX driver (do it only once) load "amplxl.dll"; # declare input table table Data IN "amplxl" "Data.xlsx" "Data": PRODUCTS <- [PRODUCTS], rate, profit, market; # read table read table Data; Write: # load the CSV driver (do it only once) load "amplcsv.dll"; # declare output table table Result OUT "amplxl" "Result.xlsx" "Result": [PRODUCTS], Make; # write table write table result_csv; The parameter after the file name in a table declaration can be either a worksheet name or a named range in both cases (reading and writing).

• Supported Languages and Files
• Running a Solver