Gmail插件setValue重载构造函数错误及字段值获取求助

Hey there! Let's break down your problems and fix them one by one.

First: Fix the setValue Overload Error #

The error you're seeing happens because Google Apps Script can't figure out which version of setValue to use—your opt_prefills elements might be undefined, null, or a non-string/non-number type (like a raw Date object). To resolve this, make sure you're always passing a string to setValue by converting the prefill value explicitly, and handle empty cases gracefully.

Here's how to update each TextInput in your createFormSection function:

// Example for dateAssigned (repeat this pattern for all TextInput fields)
var dateAssigned = CardService.newTextInput()
  .setFieldName("dateAssigned")
  .setTitle("Date Assigned")
  .setValue(opt_prefills[0] ? String(opt_prefills[0]) : ''); // Convert to string, default to empty

var AssignerName = CardService.newTextInput()
  .setFieldName("AssignerName")
  .setTitle("Assigner Name")
  .setValue(opt_prefills[1] ? String(opt_prefills[1]) : '');

// ... repeat this for all other TextInput fields

This ensures you're always passing a string (or empty string if there's no prefill data), eliminating the ambiguous constructor error.

Second: Correctly Get Field Names & Values in Gmail Add-ons #

Your current approach in submitForm is on the right track, but let's clarify the best practices:

1. Define clear field names upfront
   When creating form widgets, always use setFieldName() with a unique, descriptive key (you're already doing this well—e.g., setFieldName("dateAssigned")). This key is what you'll use to fetch the value later.

2. Fetch form data via e.formInput
   In your submit handler, e.formInput is an object that maps all your fieldName values to the user's input/selection. For example:

   var formData = e.formInput;
   var dateAssigned = formData.dateAssigned; // Same as formData['dateAssigned']
   var selectedService = formData.servicedropdown; // Works for dropdowns too!
   

   This works for all widget types (TextInput, Dropdown, Checkboxes, etc.)—the fieldName is the consistent key across all controls.

3. Handle empty or missing values
   Always validate that required fields exist before processing. Your existing try/catch block with data.forEach is good, but you can make it more explicit:

   var requiredFields = ['dateAssigned', 'CandidateOrCaseName', 'CandidateEmailOnly'];
   requiredFields.forEach(field => {
     if (!formData[field] || formData[field].trim() === '') {
       throw `Please complete the required field: ${field}`;
     }
   });
   

4. Pre-fill data safely
   When passing opt_prefills, ensure the array length matches the number of TextInput fields, and that values are either strings/numbers. If you're pulling data from Gmail (like email dates), convert it to a string first (e.g., Utilities.formatDate(emailDate, Session.getScriptTimeZone(), 'MM/dd/yyyy')).

Bonus: Simplify Date Handling for JDBC #

Your current date parsing is a bit convoluted. Instead of formatting a date string then parsing it back, you can directly pass a timestamp:

// Replace this:
stmt.setTimestamp(3, Jdbc.parseTimestamp(Utilities.formatDate(new Date(currentDate), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss')));

// With this:
stmt.setTimestamp(3, new Jdbc.Timestamp(new Date().getTime()));

This is cleaner and avoids potential formatting errors.

内容的提问来源于stack exchange，提问作者RJ Villamer