jafrancov / google-apps-scripts Goto Github PK

Hi!
I need your help,
I´m working on a .gs code. I´ve created a form in Google Script with several sections.
I need to generate a sequential number and add values from other columns.
example: CSINT-120219-0004
I`m blocked, I dont see how to solve this. Please Assist me with this code

function sequenceNumberOnFormSubmit(e) {

` //var record = addSequenceNumber();

var correo_grupal = "";
var responsables = "";
var tipo_material = "e.values[5]";
switch (tipo_material){
case "Materials for my promo (IBO Blast, social media post, print material, digital assets, etc)":
correo_grupal = "[email protected],[email protected],[email protected],[email protected]";
responsables = "Paulina/Daniela/Ximena/Jorge";
break;
case "Print Materials (Labels, flyers, brochures, business cards, etc)":
correo_grupal = "[email protected],[email protected],[email protected],[email protected],[email protected]";
responsables = "Fernando/Ximena/Paulina/Daniela/Jorge/Carlos";
break;
case "Digital Communications (IBO Blast, social media post, banners, landing page, etc)":
correo_grupal = "[email protected],[email protected],[email protected],[email protected]";
responsables = "Paulina/Jorge/Ximena/Daniela";
break;
case "Audiovisual Materials (Videos, audios, songs, etc)":
correo_grupal = "[email protected],[email protected],[email protected]";
responsables = "Fernando/Ximena/Jorge";
break;
case "Event Materials (IBO Blast, flyers, social media post, digital, print, etc)":
correo_grupal = "[email protected]";
responsables = "Todos";
break;
case "Business Forms (Autoship Order Form, Product Order Form, Price List, IBO Application & Agreement, etc)":
correo_grupal = "[email protected]";
responsables = "Fernando";
break;
case "SMS (Text messages)":
correo_grupal = "[email protected]";
responsables = "Rocio";
break;
case "Website changes and updates":
correo_grupal = "[email protected],[email protected],[email protected] "
responsables = "Enrique/Fernando/Jorge";
break;
case "Add Option or others":
correo_grupal = "[email protected]";
responsables = "Todos";
}

var sheet = SpreadsheetApp.getActiveSheet();
var row = SpreadsheetApp.getActiveSheet().getLastRow();
var record = row -1;
if (record < 10) {
record = "00" + record;
}

if (record < 100) {
    record = "0" + record;
}

var timestamp = e.values[0];
var folio = e.value[1]
var mail = e.values[2];
var name = e.values[3];
var topic = e.values[5];
var area = e.values[54];
switch (area) {
    case "Board":
        area = "BOARD";
        break;
    case "Comercial":
        area = "COM";
        break;
    case "IT":
        area = "IT";
        break;
    case "General Direction":
        area = "DIR";
        break;
    case "Vice Presidency":
        area = "VP";
        break;
    case "Logistics and Warehouse":
        area = "LW";
        break;
    case "Customer Service":
        area = "CS";
        break;
    case "Marketing":
        area = "MKT";
        break;
};

var country = e.values[55];
switch (country) {
case "International":
country = "INT";
break;
case "United States":
country = "US";
break;
case "Mexico":
country = "MX";
break;
case "South Korea":
country = "KR";
break;
case "Australia":
country = "AU";
break;
case "Canada":
country = "CA";
break;
case "Colombia":
country = "CO";
break;
case "Ecuador":
country = "EC";
break;
case "El Salvador":
country = "SV";
break;
case "Guatemala":
country = "GT";
break;
case "Mongolia":
country = "MNG";
break;
case "New Zealand":
country = "NZ";
break;
case "Hong Kong":
country = "HKG";
break;
case "Philippines":
country = "PH";
break;
case "Spain":
country = "ES";
break;
case "Taiwan":
country = "TW";
break;
case "UK":
country = "UK";
break;
}

var fulldate = date();
var folio = area + country + "-" + fulldate + "-" + record;

sheet.getRange(row, 2).setValue(folio + "-" + responsables);

var subject = "New Material Request: " + folio;

var plain_email_body = "¡Hello" + name + "!<br><br>" +
    "We received your request <b>" + topic + "-" + tipo_material + "</b> on: <i>" + timestamp + "</i><br><br>" +
    "The folio of your request is " + folio + "for any doubt or comment";
var html_body = "¡Hello" + name + "!<br><br>" +
    "We received your request <b>" + topic + "-" + tipo_material + "</b> on: <i>" + timestamp + "</i><br><br>" +
    "The folio of your request is" + folio + "for any doubt or comment";

var advancedOpts = {
    name: "Material",
    htmlBody: html_body
};
MailApp.sendEmail(mail, subject, plain_email_body, advancedOpts);

var plain_email_body_manager = "¡Hello Team!<br><br>" +
    "We have received a request of <b>" + topic + "-" + tipo_material + "</b> on: <i>" + timestamp + "</i> - Requested by: " + name +"<br><br>" +
    "The folio of the request is <b>" + folio + "</b>;
var html_body_manager = "¡Hello Team!<br><br>" +
    "We have received a request of <b>" + topic + "-" + tipo_material + "</b> on: <i>" + timestamp + "</i> - Requested by: " + name +"<br><br>" +
    "The folio of the request is <b>" + folio + "</b>;

var advancedOpts_manager = {
    name: "Material",
    htmlBody: html_body_manager
};

MailApp.sendEmail("[email protected]", subject, plain_email_body_manager, advancedOpts_manager);
MailApp.sendEmail(correo_grupal, subject, plain_email_body_manager, advancedOpts_manager);

}

function foliodate() {
var date = new Date();
var day = date.getDate();
if (day < 10) {
day = "0" + day;
}
var month = date.getMonth();
month = month + 1;
if (month < 10) {
month = "0" + month;
}
var year = date.getYear();
year = year.toString().substr(-2);
var fulldate = day + "" + month + "" + year;

return fulldate;

}`

Hola. Por favor, verificar la posibilidad de nombrar la columna (si no tiene nombre), en la parte del código relacionada:

https://github.com/jafrancov/Google-Apps-Scripts/blob/master/forms/notifyOnFormSubmit.js

    // Escribe el número de registro en la celda especificada
    // IMPORTANTE: Cambia el número 4 por el número de la columna donde lo vayas a escribir (A=1, B=2, C=3, D=4, etc.)
    hoja.getRange(renglon, 5).setValue(folioRegistro);

Ejemplo: nombrarla como ID, u otra cosa a tu elección, de ser posible. No sé si esto es lo adecuado:

// Escribe el nombre de la columna en la primera fila (si no tiene)
// IMPORTANTE: Cambia el número 5 por el número de la columna donde lo vayas a escribir (A=1, B=2, C=3, D=4, etc.)
// Reemplaza NOMBRE_DE_TU_COLUMNA por el identificador de tu columna (bar, foo, etc)
hoja.getRange(1, 5).setValue('NOMBRE_DE_TU_COLUMNA');

// Escribe el número de registro en la celda especificada
// IMPORTANTE: Cambia el número 5 por el número de la columna donde lo vayas a escribir (A=1, B=2, C=3, D=4, etc.)
hoja.getRange(renglon, 5).setValue(folioRegistro);

Esto es muy beneficioso para combinar correspondencia usando la hoja de cálculos asociada a un formulario de google docs

Gracias