Google Spread Sheet code doesn`t sent mail and consecutive number

[RosySietevidas]

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;

}`

[jafrancov]

You almost got it! Your function foliodate just need to be called, and then concatenate the other data you want, here:

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

That should do the magic, let me know if that works.

P.S. Supongo que también podemos hablarnos en español.

[RosySietevidas]

Gracias!! Haré la prueba a ver si funciona! 😁

El jue., 13 de febrero de 2020 11:12 a. m., Alex Franco < [email protected]> escribió:

You almost got it! Your function foliodate just need to be called, and the concatenate the other data you want, here:

var fulldate = foliodate();

var folio = area + country + "-" + fulldate + "-" + record;

That should do the magic, let me know if that works.

P.S. Supongo que también podemos hablarnos en español.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/jafrancov/Google-Apps-Scripts/issues/1?email_source=notifications&email_token=AGBR3WRBEXZ56I3HPNY4UVLRCV5OHA5CNFSM4KSXQGI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELVZ6QI#issuecomment-585867073, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGBR3WVXQBCSYYOKNVLGYHDRCV5OHANCNFSM4KSXQGIQ .

[RosySietevidas]

Cómo puedo evitar que me marque como error la propiedad "e.values"? Me dice que está indefinido el valor

El jue., 13 de febrero de 2020 11:12 a. m., Alex Franco < [email protected]> escribió:

You almost got it! Your function foliodate just need to be called, and the concatenate the other data you want, here:

var fulldate = foliodate();

var folio = area + country + "-" + fulldate + "-" + record;

That should do the magic, let me know if that works.

P.S. Supongo que también podemos hablarnos en español.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/jafrancov/Google-Apps-Scripts/issues/1?email_source=notifications&email_token=AGBR3WRBEXZ56I3HPNY4UVLRCV5OHA5CNFSM4KSXQGI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELVZ6QI#issuecomment-585867073, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGBR3WVXQBCSYYOKNVLGYHDRCV5OHANCNFSM4KSXQGIQ .