Using AI to Generate Test Data for Apps Script

When you write an Apps Script function, it's important to test it comprehensively to ensure that it works correctly in all scenarios.

AI chatbots like Anthropic's Claude can help you identify test scenarios and create test cases to verify that your script functions correctly. In this tutorial, we'll use Claude to generate test cases for a function that merges consecutive cells in each row if they contain identical values.

In the tutorial I linked above, I used a spreadsheet containing test data to test the script. Each row in the spreadsheet contained data for testing a specific scenario. We'll now use AI to create such a sheet.

Prerequisites

This tutorial assumes that you are familiar with Generative AI chatbots like Claude (or ChatGPT) and writing simple scripts using Apps Script.

Step 1: Start a new chat with Claude

Start a new chat by opening https://claude.ai/new. I like using the Claude Sonnet 3.5 model for coding Apps Script scripts because it works really well for me. As of Nov 2024, this is the model that is selected by default when you begin chatting with Claude.

Step 2: Enter your prompt

Next, we'll prompt Claude to create an Apps Script function that generates test cases for various scenarios.

Here is the prompt I used:

Only output exactly what is requested. Do not explain yourself or output a preamble.

Create test data for a Google Apps Script that merges consecutive cells containing the same value in each row.

Generate 20 rows X 10 columns of test data to check various scenarios. First think about comprehensive test cases and then generate data such that these scenarios can be validated.

Generate an Apps Script function that will write test data to the currently active Google Sheet. Assume that the script is bound to the spreadsheet and ensure you only ask authorization for the spreadsheet that the script is installed in. For each test case, include an in-line comment that explains what scenario is being tested. Do not include a header row.

Let's walk through the prompt in detail

When prompting AI, it is important to specify your requirements precisely to ensure that the output is exactly what you need.

First, we specify that Claude must only output what we request.

Then we specify what we want Claude to do. We describe the function that we want to test.

We ask Claude to think through 20 test scenarios to check and provide one row of test data for each scenario.

Finally, we specify how Claude must generate the test data. In this prompt, we ask Claude to generate an Apps Script function that will populate the spreadsheet with the test data. We can also ask Claude to output the test data in a tabular format that can be easily copied and pasted into your sheet.

Step 3: Submit the prompt

Once you copy and paste the prompt, submit it. Claude will generate an Apps Script function to generate test data.

Here is the code that Claude generated:

function generateTestData() {
 const sheet = SpreadsheetApp.getActiveSheet();
  const testData = [
   // Basic case: No merging needed - all values different
   ["A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"],
  
   // Single merge: Two consecutive same values
   ["A2", "A2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2"],
  
   // Multiple pairs of merges in one row
   ["A3", "A3", "C3", "C3", "E3", "E3", "G3", "G3", "I3", "I3"],
  
   // Three consecutive same values
   ["A4", "A4", "A4", "D4", "E4", "F4", "G4", "H4", "I4", "J4"],
  
   // All values in row are same
   ["A5", "A5", "A5", "A5", "A5", "A5", "A5", "A5", "A5", "A5"],
  
   // Multiple groups of varying lengths
   ["A6", "A6", "A6", "D6", "D6", "F6", "F6", "F6", "F6", "J6"],
  
   // Single values between merge groups
   ["A7", "A7", "B7", "C7", "C7", "D7", "E7", "E7", "F7", "F7"],
  
   // Merge at start and end only
   ["A8", "A8", "C8", "D8", "E8", "F8", "G8", "H8", "I8", "I8"],
  
   // Alternating single and merge groups
   ["A9", "B9", "B9", "C9", "D9", "D9", "E9", "F9", "F9", "G9"],
  
   // Special characters and numbers
   ["#1", "#1", "123", "123", "!@#", "!@#", "$$$", "$$$", "***", "***"],
  
   // Empty cells between merges
   ["A11", "A11", "", "D11", "D11", "", "", "H11", "H11", ""],
  
   // Single character merges
   ["X", "X", "Y", "Y", "Z", "Z", "A", "A", "B", "B"],
  
   // Mixed case sensitivity test
   ["TEST", "test", "Test", "TEST", "test", "Test", "ABC", "abc", "ABC", "abc"],
  
   // Spaces and trimming test
   ["A14 ", " A14", "A14", " B14 ", "B14", " B14 ", "C14", " C14", "C14 ", " C14"],
  
   // Numbers with different formats
   ["1", "1.0", "1.00", "2", "2.0", "2.00", "3", "3.0", "3.00", "4"],
  
   // Unicode characters
   ["é", "é", "ñ", "ñ", "ü", "ü", "α", "α", "β", "β"],
  
   // Very long strings
   ["Long1Long1", "Long1Long1", "Long2Long2", "Long2Long2", "Long3", "Long3", "Long4", "Long4", "Long5", "Long5"],
  
   // Mixed types
   ["100", 100, "true", true, "null", null, "1.5", 1.5, "2e2", 2e2],
  
   // Leading/trailing whitespace with numbers
   [" 123 ", "123", " 123", "456 ", " 456", "456", " 789", "789 ", " 789 ", "789"],
  
   // Special formatting test
   ["=SUM(1)", "=SUM(1)", "2/1/2024", "2/1/2024", "$100", "$100", "10%", "10%", "#REF!", "#REF!"]
 ];
  // Write the test data to the sheet
 sheet.getRange(1, 1, testData.length, testData[0].length).setValues(testData);
}

Step 4: Copy and paste the function in the Apps Script editor

• Open the spreadsheet you will use to test the script.

• Open the script editor by selecting Extensions > Apps Script.

• Delete any existing code in the editor.

• Copy and paste the function that Claude generated into the editor.

The script editor should now contain the code that Claude generated:

Step 5: Review the code to ensure that it looks reasonable

Since the code was generated by AI, you must review it carefully to ensure it's not doing anything unexpected. Here are some examples of checks that I perform:

• What data is the script accessing?

• Is the script making external calls using UrlFetch? If so, is this expected? What data is being passed externally?

• Is the script accessing sensitive data such as your Gmail messages or your files in Google Drive? If so, is this expected?

• Is the script deleting data or files? If so, is this expected?

• Is the script changing sharing settings or access permissions on any files? If so, is this expected?

In my experience, AI typically does not restrict the scope of container-bound scripts to the file in which they are installed, even when explicitly instructed to do so. Therefore, if AI omits this comment in its generated code, I manually add the //@OnlyCurrentDoc comment to the script to explicitly limit its access scope to the bound file.

//@OnlyCurrentDoc
function generateTestData() {
 const sheet = SpreadsheetApp.getActiveSheet();
  const testData = [
   // Basic case: No merging needed - all values different
   ["A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"],
  
   // Single merge: Two consecutive same values
   ["A2", "A2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2"],
  
   // Multiple pairs of merges in one row
   ["A3", "A3", "C3", "C3", "E3", "E3", "G3", "G3", "I3", "I3"],
  
   // Three consecutive same values
   ["A4", "A4", "A4", "D4", "E4", "F4", "G4", "H4", "I4", "J4"],
  
   // All values in row are same
   ["A5", "A5", "A5", "A5", "A5", "A5", "A5", "A5", "A5", "A5"],
  
   // Multiple groups of varying lengths
   ["A6", "A6", "A6", "D6", "D6", "F6", "F6", "F6", "F6", "J6"],
  
   // Single values between merge groups
   ["A7", "A7", "B7", "C7", "C7", "D7", "E7", "E7", "F7", "F7"],
  
   // Merge at start and end only
   ["A8", "A8", "C8", "D8", "E8", "F8", "G8", "H8", "I8", "I8"],
  
   // Alternating single and merge groups
   ["A9", "B9", "B9", "C9", "D9", "D9", "E9", "F9", "F9", "G9"],
  
   // Special characters and numbers
   ["#1", "#1", "123", "123", "!@#", "!@#", "$$$", "$$$", "***", "***"],
  
   // Empty cells between merges
   ["A11", "A11", "", "D11", "D11", "", "", "H11", "H11", ""],
  
   // Single character merges
   ["X", "X", "Y", "Y", "Z", "Z", "A", "A", "B", "B"],
  
   // Mixed case sensitivity test
   ["TEST", "test", "Test", "TEST", "test", "Test", "ABC", "abc", "ABC", "abc"],
  
   // Spaces and trimming test
   ["A14 ", " A14", "A14", " B14 ", "B14", " B14 ", "C14", " C14", "C14 ", " C14"],
  
   // Numbers with different formats
   ["1", "1.0", "1.00", "2", "2.0", "2.00", "3", "3.0", "3.00", "4"],
  
   // Unicode characters
   ["é", "é", "ñ", "ñ", "ü", "ü", "α", "α", "β", "β"],
  
   // Very long strings
   ["Long1Long1", "Long1Long1", "Long2Long2", "Long2Long2", "Long3", "Long3", "Long4", "Long4", "Long5", "Long5"],
  
   // Mixed types
   ["100", 100, "true", true, "null", null, "1.5", 1.5, "2e2", 2e2],
  
   // Leading/trailing whitespace with numbers
   [" 123 ", "123", " 123", "456 ", " 456", "456", " 789", "789 ", " 789 ", "789"],
  
   // Special formatting test
   ["=SUM(1)", "=SUM(1)", "2/1/2024", "2/1/2024", "$100", "$100", "10%", "10%", "#REF!", "#REF!"]
 ];
  // Write the test data to the sheet
 sheet.getRange(1, 1, testData.length, testData[0].length).setValues(testData);
}

Step 6: Run the code to generate test data

To run the script, click the Play button. If this is your first time running the script, you'll be asked to authorize it. Once authorized, the script will populate your spreadsheet with test data.

Conclusion

In this tutorial, I showed you how to use AI to generate data for testing the accuracy of your Apps Script.