Automatic split text to columns on form submit

[WESLEY RENTON]

Hi there. 

I have been using ChatGPT to try and help me write a script that will automatically trigger when someone submits a new Google form. 

A person's coordinates are added to column E, I am needing those coordinates split into 2 separate columns (Column F = latitude, Column G = Longitude)... I have attached an image of what the sheet will look like.  

Here is an example of one of the many code examples ChatGPT has done for me, as well as the error code I am getting.  

Can anyone help?!

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Needs assistance");
  var lastColumn = sheet.getLastColumn();
  var editedRow = sheet.getLastRow();
  var editedValue = e.range.getSheet().getRange(e.range.getRow(), 5).getValue(); // Adjusted for column E
 
  // Split the edited value by a comma (",")
  var splitValues = editedValue.split(",");
 
  // Clear the edited cell
  sheet.getRange(editedRow, lastColumn).clearContent();
 
  // Get the range for the split values
  var splitRange = sheet.getRange(editedRow, lastColumn + 1, 1, splitValues.length);
 
  // Set the split values to the range
  splitRange.setValues([splitValues]);
}

Error

TypeError: Cannot read properties of undefined (reading 'source')

onFormSubmit

@ splittext.gs:5

[cwl...@gmail.com]

I was able to get it to work with a couple tweaks. Hope this helps or puts you on the right path!

function onFormSubmit(e) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Needs assistance");

  var lastColumn = sheet.getLastColumn(); //this is getting the lastCol of the sheet, not the row; still useful for clearing content

  var editedRow = sheet.getLastRow();

  var editedValue = e.range.getValues()[0][4] //just grab the value when it is submitted rather than go back to the sheet again

  // Split the edited value by a comma (",")

  var splitValues = editedValue.split(",");

 

  // Clear the edited cell

  sheet.getRange(editedRow, lastColumn).clearContent();

 

  // Get the range for the split values

//var splitRange = sheet.getRange(editedRow, lastColumn + 1, 1, splitValues.length); //bad idea -- see next note

  var splitRange = sheet.getRange(editedRow, 6, 1, splitValues.length); //hard code the column, otherwise it will always grow to the right

//because you have headers for lat/long, your starting "lastColumn" will be Col G. If you then set the values using lastColumn +2, the split lat/long

//will go into cols H & I. And then Col I is your new "lastColumn". etc etc

 

  // Set the split values to the range

  splitRange.setValues([splitValues]);

}

[WESLEY RENTON]

Hi there, I tried this and ended up with the same issue 

Error

TypeError: Cannot read properties of undefined (reading 'range')

onFormSubmit

@ splittext.gs:5

Note sure what to do.

[CBMServices Web]

Change that line to this:

var editedValue = e.values;

The message you have received may contain information which is confidential, private or privileged in nature. If you are not the intended recipient, you may not peruse, use, disseminate, distribute, copy, store or process this information or any attachments to the message. If received in error, please notify the sender immediately by e-mail, facsimile or telephone and, thereafter, return and/or destroy the original message. Any views expressed in the message are those of the sender unless otherwise stated.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cfeef802-a099-4de9-a339-0522c839a4c1n%40googlegroups.com.

[Rafał Chałgasiewicz]

You most likely try to run onFormSubmit manually from the script editor. The onFormSubmit runs properly only when triggered by actual form submission, so when debugging submit your form.

In your sript there's no need to to call various Spreadsheet methods, try the below which adds the split ccordinated to the same sheet as the original form response was recorded.

function onFormSubmit(e) {

  var range = e.range

  var values = e.values

  try {

    var coordinates = values[4].split(",") //assuming your coordinates are the 4th index (column E) and always contain comma

    var writeRange = range.getSheet().getRange(range.getRow(), values.length + 1, 1, coordinates.length)

    writeRange.setValues([coordinates])

  } catch (error) {

    console.log(error)

  }

}