Question 303408 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Google App Script command for automatic editing

Sorry but I don’t understand much about programming.
I’m trying to merge two codes and make them autonomous in functioning when a new response arrives from the google form.

Everything works except this part:
function onEdit1(e)
in this case the RANDOM code is general if I directly modify the sheet, instead I would like the activation to be done automatically each time a new response is sent.

What code should I use?
Thank you very much indeed.

Mauro

var SHEETNAME = "Compleanno2024"
var ID_COLUMN = 13;
var ID_LENGHT = 4;


function randomID () {
  var ALPHABET = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFHIJLMNOPQRSTUVWXYZ'
  var rtn = '2024-';
  for (var i = 0; i < ID_LENGHT; i++)  {
    rtn += ALPHABET.charAt(Math.floor(Math.random() * ALPHABET.length));
  }
  return rtn;
}


function onEdit(e) {
  onEdit1(e)
  onEdit2(e)
}  

function onEdit1(e) {

  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();

  if(row>1 && col==7){
    as.getRange(row, 13).setValue(randomID());
  }
}

function onEdit2(e) {
    var range = e.range;
    var responseArray = e.values;
    var countryCode = "39";

    //For this part, you just need to change the index[] to the specific column of mobile number in your spreadsheet, count starting from 0, equivalent to column A. 
    //In my sample, the column of mobile number on my spreadsheet is at column G, so the index I indicated was 6.
    responseArray[6] = countryCode + e.values[6]; 
    //A Range object is representing the cell or range of cells that were last added/inputted in the spreadsheet from the form.
    range.setValues([responseArray]);
}

I attach an example file…
In the second line the fields were taken from the Google form submission and the unique code was not generated. Column M

In line 3 I only copied a part of my telephone number, entering it manually, and in column M the unique code of the "function onEdit1(e) {" appeared

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is the code changed as per your instruction...

    var SHEETNAME = "Compleanno2024"
var ID_COLUMN = 13;
var ID_LENGHT = 4;


function randomID () {
  var ALPHABET = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFHIJLMNOPQRSTUVWXYZ'
  var rtn = '2024-';
  for (var i = 0; i < ID_LENGHT; i++)  {
    rtn += ALPHABET.charAt(Math.floor(Math.random() * ALPHABET.length));
  }
  return rtn;
}


function onEdit1(e) {

  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();

  if(row>1 && col==7){
    as.getRange(row, 13).setValue(randomID());
  }
}

function onEdit2(e) {
    var range = e.range;
    var responseArray = e.values;
    var countryCode = "39";

    //For this part, you just need to change the index[] to the specific column of mobile number in your spreadsheet, count starting from 0, equivalent to column A. 
    //In my sample, the column of mobile number on my spreadsheet is at column G, so the index I indicated was 6.
    responseArray[6] = countryCode + e.values[6]; 
    //A Range object is representing the cell or range of cells that were last added/inputted in the spreadsheet from the form.
    range.setValues([responseArray]);
}

    enter image description here


  2. 0

    Use an Installable Trigger with On Form Submit

    The simple trigger onEdit(e) will not work with Google Form entries. Instead, use an installable trigger with on form submit event type.

    Delete:

    function onEdit(e) {
  onEdit1(e)
  onEdit2(e)
}

    <— Update —>

    You can combine both onEditN() functions into one so that you will only need to setup one On Form Submit trigger. Your code should look like this:

    var SHEETNAME = "Compleanno2024"
var ID_COLUMN = 13;
var ID_LENGHT = 4;


function randomID () {
  var ALPHABET = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFHIJLMNOPQRSTUVWXYZ'
  var rtn = '2024-';
  for (var i = 0; i < ID_LENGHT; i++)  {
    rtn += ALPHABET.charAt(Math.floor(Math.random() * ALPHABET.length));
  }
  return rtn;
}

function onEditCombined(e) {
    var range = e.range;
    var responseArray = e.values;
    var countryCode = "39";

    //For this part, you just need to change the index[] to the specific column of mobile number in your spreadsheet, count starting from 0, equivalent to column A. 
    //In my sample, the column of mobile number on my spreadsheet is at column G, so the index I indicated was 6.
    responseArray[6] = countryCode + e.values[6]; 
    range.setValues([responseArray]);

    const as = e.source.getActiveSheet();
    const row = e.range.getRow();
    if (row > 1 && responseArray[6] != "") {
      as.getRange(row, 13).setValue(randomID());
    }

}

    And then add an installable trigger for each of your remaining onEditN(e) functions. You may do it by clicking on triggers:

    enter image description here

    And then + Add Trigger (lower right button). After wards, use the following setup for your onEdit1(e) and onEdit2(e):

    enter image description here

    The triggers page should then look like this:

    enter image description here

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search