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

Javascript – show the user the id of the ticket submitted on google forms using appscript

i’m trying to create a form for 502 users to submit tickets on google forms to show the id of the ticket to the user as a confirmation message and to the team who is going to solve it the id is added correctly to the sheet but to the user it shows the id of the previously submitted form
this is the code i know the problem is in getLastRow() so how to make it work please

function onFormSubmit(e) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const idColumn = 1;
  
   const ticketID = 'T-' + Utilities.formatString('%04d', lastRow);
  // const ticketID = lastRow
  
  sheet.getRange(lastRow, idColumn).setValue(ticketID);

  
  Logger.log('New ticket ID: ' + ticketID);

  // Update the confirmation message in the form
  const form = FormApp.openById('1oxTnzU5NRnzAlNFMmyehSf9vkORLs9vj5-XGGDvfqM0'); 
  const message = 'Thank you! Your ticket number is: ' + ticketID;
  form.setConfirmationMessage(message);
}

Response sheet

Tags:

2

Answers


  1. 0

    Looks like the problem appears with getLastRow(). It retrieves the last row of the sheet, which is not necessarily the row that was just submitted. This is because the last row might be different from the row where the new form response was added.

    You may use the e event from the submit, and get the row number where the new form response was added.

    function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the row number where the new form response was added
  var row = e.range.getRow();
  const idColumn = 1;

  const ticketID = 'T-' + Utilities.formatString('%04d', row);
  
  sheet.getRange(row, idColumn).setValue(ticketID);
  
  Logger.log('New ticket ID: ' + ticketID); 
}
    Login or Signup to reply.
  2. 0

    User ID of the Ticket Submitted on Google Forms Fixed

    I tried it, and it worked for me. I included an additional ticket ID variable. The main issue was that the problem occurred because the header was delayed by the row.

    function onFormSubmit(e) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const idColumn = 1;
  
   const ticketID = 'T-' + (Utilities.formatString('%04d', lastRow));
 
   // I added this ticketIdDuplicate variable to get the desired response in Google Forms.
   const ticketIdDuplicate = 'T-' + (Utilities.formatString('%04d', lastRow + 1));
 
 
  sheet.getRange(lastRow, idColumn).setValue(ticketID);
 
  
  Logger.log('New ticket ID: ' + ticketID);
 
  // Update the confirmation message in the form
  const form = FormApp.openById('1oxTnzU5NRnzAlNFMmyehSf9vkORLs9vj5-XGGDvfqM0');
 
  // Insert the duplicate variable here.
  const message = 'Thank you! Your ticket number is: ' + ticketIdDuplicate;
  form.setConfirmationMessage(message);
}

    Sample Output 1

    Sample Output 2

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

Back To Top
Search