A Small Experiment Using AI - Part 2

Jeff Lawrence - Common Grant Application - June 2026

Introduction

We had a grantmaker that had an evaluation process underway, and wanted to periodically download a new report during the evaluation process and identify changes from report to report, and use criteria from the first report to tag fields in subsequent reports. More specifically they wanted to:

Identify basic changes between the downloads (new evaluations, comments, scores).
Identify and highlight comments, suggestions and concerns (funding amounts, specific actions, high praise). They initially color coded the first report to identify per question and general notes that were positive, neutral, negative or informational. After the first report they wanted subsequent reports to automatically be color coded in a similar manner.
Group scores and notes by evaluator, to facilitate conversations with individual evaluators.

They weren't going to be able to do this with our existing custom report capability so they contacted us, and as an experiment we used AI to take the information and generate the results that they needed.

We thought other grantmakers might be interested in some of the things we learned from this experiment, so we wrote a simple step-by-step set of instructions of what we did and then show the results.

Overview of Steps

The process was broken down into five steps...

Generate the custom report.
Choose and startup an AI tool.
Prepare files. Learn rules from the old file. Process the new file with rules learned from the old file.
Merge the old and new files and find what has changed and what has stayed the same.
Expand and pivot the columns into rows of individual evaluators.

The original input files and output files were generated in the following order.

old.csv - Starting file exported from our system.
old.xlsx - Starting file as .xlsx
new.csv - New file with addtional changes to the scores and notes.
new.xlsx - New file as .xlsx.
new_colored.xlsx - New file with tagging rules learned from old file applied to the new file.
new_merged.xlsx - Old and new files merged together with any changes between the two files identified.
new_by_evaluator.xlsx - Reorganized file so the information is presented by evaluators.

The file old.xlsx is:

The cell colors are defined as follows:

Empty cell
Yellow indicates a more significant comment or critique.
Amber indicates a comment bringing up a possible question or suggestion, but not providing specific grant award guidance.
Green indicates a highly supportive, positive comment.

The file new.xlsx is:

Step 1 - Generate the custom report.

Went to the Evaluations page.
Selected the View: All Evaluation Scores and Notes
Clicked on the button: Export All Evals 1 By Column
Exported the results to a CSV file desktop as old.csv or new.csv (depending on the need) to our desktop.
Imported the files from our desktop into Excel and then saved them as Excel files, old.xlsx and new.xlsx.

For purposes of this experiment we anonymized names, application numbers, etc. The old.xlsx and new.xlsx files are displayed below. Because of the possible variation of evaluators for each application, and the possible length of questions and names, we use a notation of "E" for evaluator and "Q" for question in the column headers to reduce some clutter.

Step 2 - Choose and startup an AI tool.

We chose to use Claude AI since we started using it for our help and support chatbot. We have some familiarity with this tool, although we are sure any other tool would work just as well. Some popular tools include:

Tool	Company	Website
ChatGPT	OpenAPI	www.chatgpt.com
Claude	Anthropic	www.claude.ai
CoPilot	Microsoft	www.copilot.com
Gemini	Google	gemini.google.com

Step 3 - Prepare files. Learn rules from old file. Process new file with rules learned from old file.

The prompt was...

Treat this as file A...

We uploaded the older (old.xlsx) file.

Treat this as file B...

I uploaded the newer (new.xlsx) file.

Remove the E* Name columns (if they exist) on the right hand sideof file A and file B
Take file A, and sort in ascending order by the column header evaluation group name followed by the app #.
Take file B, and sort in ascending order by the column header evaluation group name followed by the app #.
Read file A and train on the already set cell colors in the columns "* per question" and "* general notes" where * is a wildcard for future use. Pink indicates an empty cell. Yellow indicates a more significant comment or critique. Orange indicates a comment bringing up a possible question or suggestion, but not providing specific grant award guidance. Green indicates highly supportive, positive comment.
Exclude changing anything in the header row with the column titles.
For file B, using what has been learned from file A, set the cell colors for "* per question" and "* general notes" as necessary.
For file B, set the cell color in the columns "*Score*" to pink for empty cells.

The result was...

All verifications pass. Let me present both files. Both files are ready. Here's a summary of what was done:

File A (March 15 — sorted only): The "Sort March 15" sheet was sorted ascending by Evaluation Group Name, then App #. Header row and legend rows were preserved in place.
File B (March 18 — sorted + colored): The "Sort March 18" sheet was sorted the same way, then colors were applied based on patterns learned from File A:

Pink — Empty cells in all * Per Question Notes, * General Notes, and * Score * columns
Green — Highly supportive, positive notes (e.g., "Excellent; strong partnership", "Strong proposal...")
Yellow — Significant critique (e.g., "Program A will support about 10%... No USC staff", "Staffing has been reduced", "Unfortunately, I cannot make sense of the budget")
Amber — Questions or suggestions without specific grant guidance (e.g., "What are the staff roles?", "Recruitment strategies not explained", "Could be strengthened by...")

The header row (row 1) was excluded from all color changes throughout.

We downloaded file old_sorted.xlsx

We downloaded file new_colored.xlsx

The file new _colored.xlsx file is:

Step 4 - Merge old and new files and find what has changed and what has stayed the same.

The prompt was...

Merge file A and B together into a new file named C using the following rules.
Exclude the header row from the following rules.
If a row in file A and file B have the same app. # but any content has changed (color or values in the fields) put the row from file B into file C and mark it as changed for below.
If a row in file B doesn't exist in file A as determined by app. # put the row from file B in file C and mark it as from file B for below.
If a row in file A doesn't exist in file B as determined by app. # put the row from file A into file C and mark it as from file A for below.
If a row in file A and file B are the same as determined by app. # and no content has changed (color or values in the fields) put the row from file B into file C and mark it as unchanged for below.
Add a column or reuse a the column “row status” to file C that indicates if a row is changed, unchanged, from file A, from file B.
After the merge sort file C in ascending order by the column header evaluation group name followed by the app #.
Save file C with the same name as file B with a "_merged" appended.

The result was...

File is ready. Here's a summary of what was done:
Merge results — 70 total rows: Changed (6 rows): These had new evaluator scores and/or per-question notes added in File B that weren't in File A. File B's version was used. Unchanged (24 rows): Values were identical between File A and File B. File B's version (with coloring) was used. From File A / From File B: None — all 30 apps appeared in both files. "Row Status" column was added as column with color-coding:

Changed — gold
Unchanged — light gray
From File A — light blue (none this time)
From File B — light green (none this time)

The file is sorted ascending by Evaluation Group Name then App #, and all legend rows are preserved at the bottom.

We downloaded file new_merged.xslx

The file new _merged.xlsx file is:

Step 5 - Expand and pivot the columns into rows of individual evaluators.

The prompt was...

Duplicate each row n times so that one row contains the information for one evaluator. Unique evaluators are specified by "E*" where "*" is an evaluator id (a number from 1 to n), associated with one evaluator name.
Unique questions are specified by "Q*" where "*" is an question id (a number from 1 to m), associated with one question. The columns to the right of the Q* relate to that question until the next Q*, except for the "total score *" and row status columns at the right hand side of the spreadsheet.
For each row include the app. #, organization name, amount requested, row status, evaluation group name and the following fields related to the particular evaluator and questions scored and annotated by the evaluator id starting with E1 Name and then followed by 1 to m question related sections for E1 including: Q1, E1 Score (Weighted), E1 Per questions Notes, E1 General Notes, Q2, etc.
Do not include "Total Score *" columns.
Map the results to new header names as follows: app. # => App #, organization name => Organization Name, amount requested => Amount Requested, row status => Row Status, evaluation group name => Evaluation Group Name, E1 Name => Evaluator Name, Q1 => Q1, E1 Score (Weighted) => Score (Weighted), E1 Per Question Notes => Per Question Notes, E1 General Notes => General Notes.
Sort the rows by ascending Evaluator Name and then App # within each evaluator name.

The result was...

Done! Here's a summary of what was produced:
89 rows — each original row duplicated once per evaluator who has a name assigned
22 columns — App # · Organization Name · Amount Requested · Row Status · Evaluation Group Name · Evaluator Name · Q1 · Score (Weighted) · Per Question Notes · Q2 · Score · Notes · Q3 · Score · Notes · Q4 · Score · Notes · Q5 · Score · Notes · General Notes Cell colors preserved from the source (pink, green, yellow, amber) on score and notes columns
Sorted ascending by Evaluator Name, then App # within each evaluator
Total Score columns excluded as requested

We downloaded file new_by_evaluator.xlsx

The file new_by_evaluator.xlsx is:

This simple experiment further highlights the power of combining our exportable custom reports with external AI tools to do complex data manipulations and visualizations.

Caution

There is a serious caveat in all of this. Applicants have a certain expectation about the privacy of the information they provide thru our system to a grantmaker. We have a policy for data privacy and security, which we take very seriously and adhere to.

Grantmakers may have a different set of data privacy and security policies. Those policies set the expectations with applicants about how grantmakers will manage and protect their information. Those expectations are established and maintained by the individual grantmakers and we don't have any control over that.

Exporting data out via a custom report takes it out of the confines of our system and injecting it into an AI prompt potentailly risks it becoming part of the training data used by AI, which then means it may become publicly available in some way. This may or may not be a problem for applicants. Data privacy in AI is a complicated and evolving topic, but it is essential that you think through what information you may be supplying to AI, and if it gets into the public domain, will that become a problem.

In this experiment the information supplied was minimal, and judged to be okay if it got into the public domain.

To read the previous part in this series: "A Small Experiment Using AI - Jeff Lawrence - April 2026"