Error With Email Change Notifications Using Project Tracker Template

Question

Short Version:

I am using the code from the Project Tracker Template to send out emails showing the change in a status for a field (Contact Name changed from: Billy -> Susan).

Everything works perfectly expect when I have a field that is a Date instead of a String. If I have a Date field in the code I get the following error:

'string' value is expected for 'NewValue' field in model 'SystemOrdersHistory', but found object. Error: 'string' value is expected for 'NewValue' field in model 'SystemOrdersHistory', but found object. at onSystemOrdersSave_ (Datasources:218) at models.SystemOrders.onSaveEvent:1

Modifying records: (Error) : 'string' value is expected for 'NewValue' field in model 'SystemOrdersHistory', but found object.

(Error) : 'string' value is expected for 'NewValue' field in model 'SystemOrdersHistory', but found object.

Any help would be greatly appreciated!

Long Version

I am using the code below (adjusted to fit the names of my models and fields).

Whenever I add a Date field (ex: DeliveryDate) to the function "notifyAboutItemChanges_" function and the "onSystemOrdersSave_" function I get the error about "expecting a string, but found an object".

Note: The OldValue and NewValue fields in the "History" model are both Strings.

Notifications Server Script:

/**
 * Sends email.
 * @param {!string} to - email address of a recipient.
 * @param {!string} subject - subject of email message.
 * @param {!string} body - body of email message.
 */
function sendEmail_(to, subject, body) {
  try {
    MailApp.sendEmail({
      to: to,
      subject: subject,
      htmlBody: body,
      noReply: true
    });
   } catch (e) {
    // Suppressing errors in email sending because email notifications
    //   are not critical for the functioning of the app.
    console.error(JSON.stringify(e));
  }
}


/**
 * Sends email notification about recent project item changes to item owner
 *     and assignee.
 * @param {!Array<ItemHistory>} changes - list of recent project item changes.
 */
function notifyAboutItemChanges_(changes) {
  if (!changes || changes.length < 2) {
    return;
  }

  var settings = getAppSettingsRecord_()[0];

  if (!settings.EnableEmailNotifications) {
    return;
  }

  var data = {
    appUrl: settings.AppUrl,
    itemShowName: changes[0].ShowName,
    itemUsersPosition: changes[0].UsersPosition,
    itemDeliveryInfo: changes[0].DeliveryInfo,
    itemDeliveryDate: changes[0].DeliveryDate,
    itemKey: changes[0]._key,
    itemName: changes[0].Name,
    modifiedBy: changes[0].ModifiedBy,
    changes: changes
  };

  // Email subject.
  var subjectTemplate =
      HtmlService.createTemplate(settings.NotificationEmailSubject);

  subjectTemplate.data = data;

  var subject = subjectTemplate.evaluate().getContent();

  // Email body.
  var emailTemplate =
      HtmlService.createTemplate(settings.NotificationEmailBody);

  emailTemplate.data = data;

  var htmlBody = emailTemplate.evaluate().getContent();

  sendEmail_('[email protected]', subject, htmlBody);

Datasources Server Script:

/**
 * Item key URL parameter.
 */
var ITEM_KEY = 'itemKey';


/**
 * Checks that Application Settings record already exists.
 *     Otherwise creates a new one.
 * @return {!Array<AppSettings>} app settings record as an array.
 */
function getAppSettingsRecord_() {
  var newQuery = app.models.AppSettings.newQuery();
  var settingsRecords = newQuery.run();

  if (settingsRecords.length > 1) {
    console.warn('There is more than one(%s) App Settings entries' + 
                 'in the database', settingsRecords.length);
  }

  if (settingsRecords.length === 0) {
    var settingsRecord = app.models.AppSettings.newRecord();

    settingsRecord.AppUrl = ScriptApp.getService().getUrl();
    settingsRecord.NotificationEmailSubject =
      'A change has been made to <?= data.itemShowName?>: <?= data.itemUsersPosition?>';

    settingsRecord.NotificationEmailBody =
      'Hello!\n<br/>\n<p><b><?= data.modifiedBy ?></b> ' +
      'made the following changes: </p>\n' +
      '<? for (var i = 1; i < data.changes.length; i++) {\n' +
      '\tvar change = data.changes[i]; ?>\n' +
      '\t<b><?= change.FieldName ?>: </b>\n' +
      '\t<? if (change.FieldName === "Comment") { ?>\n' +
      '\t\t<div style="white-space: pre-line;"><?= change.NewValue ?></div>' +
      '\n\t<? } else { ?>\n ' +
      '\t\t<?= change.OldValue ?> &#8594; <?= change.NewValue ?>' +
      '\n\t<? } ?>\n\t<br/>\n' +
      '<? } ?>\n<br/>\n' +
      '<a href="<?= data.appUrl ?>?' + ITEM_KEY + '=<?= data.itemKey ?>' +
      '#EditItem" target="_blank">Go to the project item</a>';

    app.saveRecords([settingsRecord]);

    return [settingsRecord];
  } else {
    return settingsRecords;
  }
}

/**
 * Populates project record with required data on project create event.
 * @param {!Project} project - project being created.
 */
function onProjectCreate_(project) {
  var date = new Date();

  project.CreatedDate = date;
  project.ModifiedDate = date;
  project.ModifiedBy = currentUserEmail_();
}


/**
 * Audits project on changes.
 * @param {!Project} project - project being modified.
 */
function onProjectSave_(project) {
  project.ModifiedDate = new Date();
  project.ModifiedBy = currentUserEmail_();
}


/**
 * Populates project item with required data on item create event, adds
 *     comment entry to the project item history.
 * @param {!SystemOrders} SystemOrders - project item being created.
 */
function onSystemOrdersCreate_(SystemOrders) {
  var date = new Date();
  var editor = currentUserEmail_();

  if (SystemOrders.Comment) {
    SystemOrders.Comment = SystemOrders.Comment.trim();
  }

  SystemOrders.CreatedDate = date;
  SystemOrders.Owner = editor;
  SystemOrders.ModifiedDate = date;
  SystemOrders.ModifiedBy = editor;

  if (SystemOrders.Comment) {
    var history = app.models.SystemOrdersHistory.newRecord();

    history.CreatedBy = currentUserEmail_();
    history.CreatedDate = new Date();
    history.FieldName = 'Comment';
    history.NewValue = SystemOrders.Comment;

    app.saveRecords([history]);

    SystemOrders.History.push(history);
  }

}


/**
 * Calculates history entries sum for {Array<SystemOrders>}.
 * @param {!number} historySum - the accumulated number of history entries
 *     previously returned in the last invocation of the callback, or
 *     initialValue, if supplied.
 * @param {!SystemOrders} SystemOrders - the current {SystemOrders} being
 *     processed in the array.
 * @return {!number} history entries sum.
 */
function sumHistory_(historySum, SystemOrders) {
  return historySum + SystemOrders.History.length;
}


/**
 * Calculates potential project deletion impact.
 * Throws an error if there is no project with the key provided.
 * @param {!string} projectKey - project key to calculate deletion impact.
 */
function getDeleteProjectImpact(projectKey) {
  var projectQuery = app.models.Project.newQuery();

  projectQuery.prefetch.Items._add();
  projectQuery.prefetch.Items.History._add();
  projectQuery.filters._key._equals = projectKey;

  var projects = projectQuery.run();

  if (projects.length === 0) {
    throw new Error('Project with key ' + projectKey + ' was not found.');
  }

  var SystemOrderss = projects[0].Items;

  return {
    affectedItems: SystemOrderss.length,
    affectedHistory: SystemOrderss.reduce(sumHistory_, 0)
  };
}


/**
 * Checks that project item readonly fields were not modified.
 * Throws an error if user attempts to modify read only fields.
 * @param {!SystemOrders} record - modified project item.
 * @param {!SystemOrders} oldRecord - project item before modification.
 */
function validateItemChange_(record, oldRecord) {
  var readonlyFields = [
    'CreatedDate',
    'ModifiedBy',
    'ModifiedDate',
    'Owner'
  ];

  for (var i = 0; i < readonlyFields.length; i++) {
    var field = readonlyFields[i];
    var newValue = record[field];
    var oldValue = oldRecord[field];
    var isDate = newValue instanceof Date && oldValue instanceof Date;

    if (isDate === true) {
      newValue = record[field].getDate();
      oldValue = oldRecord[field].getDate();
    }

    if (newValue === oldValue) {
      continue;
    }

    throw new Error(field + ' field is read only');
  }
}


/**
 * Handles project item change event, creates history entries for each changed
 *     field.
 * @param {!SystemOrders} record - modified project item.
 * @param {!SystemOrders} oldRecord - project item before modification.
 */
function onSystemOrdersSave_(record, oldRecord) {
  validateItemChange_(record, oldRecord);

  var editableFields = [
    'ShowName',
    'UsersPosition',
    'DeliveryInfo',
    'DeliveryDate'
    ];

  var editor = currentUserEmail_();
  var date = new Date();
  var changes = [record];

  record.ModifiedBy = editor;
  record.ModifiedDate = date;

  for (var i = 0; i < editableFields.length; i++) {
    var field = editableFields[i];
    var newValue = record[field];
    var oldValue = oldRecord[field];

    if (newValue !== oldValue) {
      var history = app.models.SystemOrdersHistory.newRecord();

      history.Item = record;
      history.CreatedBy = editor;
      history.CreatedDate = date;
      history.FieldName = field;
      history.NewValue = newValue;
      history.OldValue = oldValue;

      changes.push(history);
    }
  }

  app.saveRecords(changes);

  notifyAboutItemChanges_(changes);
}


/**
 * Counts project items by some grouping criteria(field).
 * @param {!string} projectKey - project key to calculate stats.
 * @param {!string} grouping - project item field to group items by.
 * @param {!Array<string>} groupingValues - possible field values.
 * @return {!Array<SystemOrderssBreakdown>} grouped project items counts.
 */
function getSystemOrderssBreakdown_(projectKey, grouping, groupingValues) {
  if (!grouping || !groupingValues || groupingValues.length === 0) {
    return [];
  }

  var itemsQuery = app.models.SystemOrders.newQuery();

  itemsQuery.prefetch.Project._add();
  itemsQuery.filters.Project._key._equals = projectKey;

  var items = itemsQuery.run();

  if (items.length === 0) {
    return [];
  }

  var records = [];
  var map = {};

  for (var i = 0; i < items.length; i++) {
    var itemGrouping = items[i][grouping];

    if (!map[itemGrouping]) {
      map[itemGrouping] = 0;
    }

    map[itemGrouping]++;
  }

  for (i = 0; i < groupingValues.length; i++) {
    var breakdownRecord = app.models.SystemOrderssBreakdown.newRecord();
    var groupingValue = groupingValues[i];

    breakdownRecord.Grouping = groupingValue;
    breakdownRecord.ItemsCount = map[groupingValue] || 0;

    records.push(breakdownRecord);
  }

  return records;
}

Pavel Shkleinik Pavel Shkleinik · Accepted Answer · 2017-07-25T20:57:09

It fails here:

// history.NewValue and history.OldValue are strings
// newValue and oldValue can be of any type (Boolean, Number, Date,
// but not a relation as of now)
// You are getting an exception because you are not casting types
history.NewValue = newValue;
history.OldValue = oldValue;

You can fix it by adding fields of each possible type to your history model (NewStringValue, NewDateValue, NewBoolValue, NewNumberValue, OldStringValue...). With that approach you'll get benefits of strong typing, but your code and UI will become significantly more complex...
You can store all your fields' history as strings(like you are doing now), but in this case you'll need to think about formatting and localization in advance:

function fieldToString(field, fieldValue) {
  // TODO: pass field metadata to individually handle
  // different data types.
  return fieldValue !== null ? fieldValue.toString() : null;
}

...
history.NewValue = fieldToString(field, newValue);
history.OldValue = fieldToString(field, oldValue);
...

Error With Email Change Notifications Using Project Tracker Template

1 Answers