compare spreadsheet 1 and spreadsheet 2, then send email problem

Question

Who can help me. I would like to say sorry if I can't explain everything correctly as English is not my first language. What I am doing is to create a Google Forms. This are the example.

1st Part:

Google Form 1 | Spreadsheet 1: In the google form, once informations entered. Spreadsheet updated each row. IT person will fix the problem according to the report. Once IT person is done, he will use Google Form 2.

2nd Part:

Google Form 2 | Spreadsheet 2: IT person use 2nd Google Form to close tickets or pending tickets. If IT person submit the form, it is sending an email to the Email Address found in the Spreadsheet 1, and also say the Ticket ID(column found on Spreadsheet 1 and 2), say Ticket Status(column found on Spreadsheet 2), say Report Problem Details(column found on Spreadsheet 1) on the email message. How to do?

This is the code I make.

function myFunction(e) {

// declare variables
   var Timestamp = e.values[0];
   var TicketID = e.values[1];
   var Email = e.values[2];
   var Status = e.values[3];

var ss = SpreadsheetApp.openById(INSERT SPREADSHEET ID 1 INSIDE);
var DetailsCol = 6; // column number of Report Problem Details from Spreadsheet 1

/* This is the part I not know how to get. I want to compare Ticket ID 
from Spreadsheet 1 to Spreadsheet 2, and if they’re same, then it returns the value of 
“Report Problem Details” that is associate to the Ticket ID match, 
and save it to Spreadsheet 2 column F, then send email with this value.*/
var ss1value = ss.getDataRange().getValues(); 
  for(var i = 0; i<data.length;i++){
    if(data[i][1] == TicketID){  //start 0 index
        return i+1;
    }
  }
ss1value.getRange(i, 5).setValue(Report Problem Details);

// Email Send
   var Subject = "Ticket Status";
   var Message = 
 '<!DOCTYPE html> ' +
 "<html><head>" +
    "<table border='1' width='300'>" +  
    "<tr><td>Case ID</td><td>" + TicketID +
    "<tr><td>Status</td><td>" + Status +
    "<tr><td>Problem Description</td><td>" + 'Details' +
    "</table>" +
MailApp.sendEmail(Email, Subject, "", {htmlBody: Message});

}

I am sorry, please not be mad. My code not working that's why I didn't posted. I'll update it above. Thank you so much. — xielieliu
Welcome. Is there a specific reason why you use 2 forms and 2 spreadsheets? What if there was just one form and one spreadsheet (with two sheets). The form is sent by the person who has the problem and updates the Form Response sheet. A script automatically copies the response to a second sheet ("support") with statof of "Open"; "support" has extra column(s) where the IT person can update the status (and maybe the date of status change, plus any other info you need). A script detects when the IT person changes the status from "Open", and an email is sent. — Tedinoz
Would you please explain how the TicketID is generated? Since the staff member is creating the form, how do you ensure that each Ticket Number is unique? — Tedinoz

Tedinoz Tedinoz · Accepted Answer · 2019-11-20T08:50:42

You are building a support ticket system using Google Forms and Google Sheets. You are having problems matching a Ticket Number from two spreadsheets.

The following scripts are offered as a way to simplify the management of the tickets so that "matching" isn't required. There are two scripts:

so5894534201(e): which should be installed as an Installable onFormSubmit trigger.
onEdit(e): a script with executes onEdit.

so5894534201(e)
The general approach is that users submit the form which updates the Form Response sheet. onFormSubmit captures the response and adds it to the "Support" sheet. It also adds an extra field "Status" to which it assigns a Data Validation rule (the options are "Open", "Pending" and "Closed"), and sets an initial value of "Open".

onEdit(e)
This watches for a change in the status on the support sheet. The anticipated action is that an email will be sent to the person who lodged the ticket. I have left this for you to complete.

function so5894534201(e) {

  // to be installaed as an Installable onFormSubmit trigger

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // reference the sheets
  var responsesheetname = "Form Responses 1";
  var response = ss.getSheetByName(responsesheetname);
  var supportsheetname = "support";
  var support = ss.getSheetByName(supportsheetname);
  var settingssheetname = "settings";
  var settings = ss.getSheetByName(settingssheetname);

  // event objects
  //Logger.log(JSON.stringify(e)); // DEBUG

  // create temporary array to hold response values
  var responsedata = [];
  responsedata.push(e.values[0]);
  responsedata.push(e.values[1]);
  responsedata.push(e.values[2]);
  responsedata.push(e.values[3]);
  responsedata.push(e.values[4]);
  responsedata.push(e.values[5]);

  // get last row on Support
  var Avals = support.getRange("A1:A").getValues();
  var supportLR = Avals.filter(String).length;
  // Logger.log("DEBUG: the last row in support = "+supportLR)

  // get the range to the next support response
  var supportRange = support.getRange(supportLR+1,1,1,6);
  // Logger.log("DEBUG: the suppport target range = "+supportRange.getA1Notation())

  // build status cell on Support
  var cell = support.getRange(+supportLR+1,7);
  // Logger.log("DEBUG: the status cell range is "+cell.getA1Notation());

  // build the data validation rule
  var settingsRange = settings.getRange("A2:A4");
  var statusrule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(settingsRange, true)
    .build();

  //set the rule
  cell.setDataValidation(statusrule);

  // Update the initial Status value 
  cell.setValue("Open");

  // Update the support range with the latest response
  supportRange.setValues([responsedata]);

}

function onEdit(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var supportsheetname = "support";
  var support = ss.getSheetByName(supportsheetname);

  // get the event objects
  //Logger.log(JSON.stringify(e)); // DEBUG
  var column = e.range.getColumn();
  var sheet = e.range.getSheet().getName();
  var newStatus = e.value; // new status value
  var oldStatus = e. oldValue // old value

  // if column = column 7 (G) && sheet = "support" && newstatus isn't Open (i.e. isn't the new support response
  if(column == 7 && sheet == supportsheetname && newStatus != "Open"){
    // do stuff
    //get the data
    var row = e.range.getRow();
    var supportdataRange = support.getRange(row,1,1,7);
    Logger.log("support data range = "+supportdataRange.getA1Notation());
    Logger.log("Edited row = "+row);
    var supportdata = supportdataRange.getValues();
    var timeStamp =  supportdata[0][0];
    var ticketNumber = supportdata[0][1];
    Logger.log("Error logged:"+timeStamp+", Ticket Number:"+ticketNumber)
    var name =  supportdata[0][2];
    var phone = supportdata[0][3];
    var email = supportdata[0][4];
    var problem =   supportdata[0][5];
    Logger.log("Name:"+name+", Phone:"+phone+", Email:"+email+", Problem:"+problem);

    // Logger.log("DEBUG: onedit was triggered the old status was "+oldStatus+", and the new status is "+newStatus)
    // send an email with this information
  }
else
  {
    // nothing to do because status wasn't chnaged from "Open"
    Logger.log("DEBUG: do nothing - status wasn't changed FROM 'Open'")

  }
}

Support sheet

Settings sheet

compare spreadsheet 1 and spreadsheet 2, then send email problem

2 Answers