0

I am new to coding with google apps script and am trying to make a simple case checker for my spreadsheet.

In this case, I want to check if the first letter of a name is capitalized, and if it isn't, I want to replace it with a corrected version (same name, just with the first letter capitalized.)

This code returns an error when trying to run it in the editor (cannot read property "range" of undefined.) I presume this is because it is using an onFormSubmit trigger, so the only way to test it would be to actually submit a form response.

When a new form response IS posted and the function is triggered, it doesn't seem to do anything on the spreadsheet's end. The new cells that contain the first name and last name (or any of the new cells for that matter) simply don't change, first-letter-capitalized or not.

When using a separate function that refers to the cells the same way (taking a range of the newly submitted cells and designating them row[numberhere] within the for loop) it reads their strings just fine.

function caseCheck(r) {
  var range = r.range;
  var data1 = range.getValue();
  for (var r in data1) {
    var row1 = data1[r];
    var firstName = row1[2].getValue;
    var lastName = row1[3].getValue;
    var firstNameC = firstName[0].toUpperCase();
    var lastNameC = lastName[0].toUpperCase();

    if (firstName[0] != firstNameC) {
      var firstNameL = firstName.length();
      var firstNameSS = firstName.substring(1,firstNameL);
      var firstNameCorrected = firstNameC + firstNameSS;
      row1[2].setValue(firstNameCorrected);
    }

    if (lastName[0] != lastNameC) {
      var lastNameL = lastName.length();
      var lastNameSS = lastName.substring(1,lastNameL);
      var lastNameCorrected = lastNameC + lastNameSS;
      row1[3].setValue(lastNameCorrected);
    }
  }
}
dwmorrin
  • 2,704
  • 8
  • 17
Toklo490
  • 3
  • 2
  • You can't run an onFormSubmit() function as a standalone function. As you can see this function needs an argument. If you just try to run it without providing an argument, it won't run since it does know what `r` is. Firstly, you need to be sure that you have register this function as onFormSubmit() method. See first answer here: https://stackoverflow.com/questions/17992718/how-to-get-onformsubmit-to-trigger-automatically – Marios Aug 06 '20 at 23:49
  • I had already set up the trigger as detailed in the post you reference. Any other ideas as to what I may be doing wrong? – Toklo490 Aug 07 '20 at 00:12

1 Answers1

2

You have several issues with the code as shown.

I think you meant to use the plural range.getValues() not range.getValue() as .getValue just returns the Object inside the top left cell of the range. If data1 is a string, then for (var r in data1) is just looping over a string, not an array of values.

Assuming that you fix data1 so it is an array of arrays, then row1 is just a JavaScript array, presumably an array of strings, so the various places where you are using e.g. row1[2].getValue (which don't have parentheses anyways) and row1[2].setValue(firstNameCorrected) shouldn't have any effect because these are strings, not Range objects.

What you need to do is mutate the data array as needed, and then call range.setValues(data) with the mutated data.

function caseCheck(r) {
  var range = r.range;
  var data = range.getValues();
  for (var r in data) {
    var row = data[r];
    // do whatever mutation to row you want
  }
  range.setValues(data);
}

As a side-note, personal preference/suggestion, this kind of imperative mutating code is very hard to read and maintain. Look into using more (and smaller) functions...

function caseCheck(r) {
  var range = r.range;
  var data = range.getValues();
  var newData = data.map(rowCapitalizeFunction);
  range.setValues(newData);
}

function rowCapitalizeFunction(row) {
  var firstName = row[2];
  // etc ... do what you need to do
  return [/* send back a new array with new values, much easier to understand! */];
}
dwmorrin
  • 2,704
  • 8
  • 17