5
\$\begingroup\$

Basically is a script to export a table from Google Sheets and send it via email. But it's not a clean script, although it works.

If there is any way of making it more simple, or instead of get all the values get the complete table and then export, that would be much more clean.

function sendFuelcount() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.setActiveSheet(sheet.getSheets()[0]);

  var nom0 = sheet.getRange("A1").getValue();
  var fuel0 = sheet.getRange("D1").getValue(); 
  var line0 = nome0 + "\t\t" + fuel0;

  var nom1 = sheet.getRange("A2").getValue();
  var fuel1 = sheet.getRange("D2").getValue();
  var data1 = sheet.getRange("C2").getValue();
  var line1 = nom1 + "\t" + fuel1 + "\t\t" + "Em: " + data1;

  var nom2 = sheet.getRange("A3").getValue();
  var fuel2 = sheet.getRange("D3").getValue();
  var data2 = sheet.getRange("C3").getValue();
  var line2 = nom2 + "\t\t" + fuel2 + "\t\t" + "Em: " + data2;

  var nome3 = sheet.getRange("A4").getValue();
  var fuel3 = sheet.getRange("D4").getValue();
  var data3 = sheet.getRange("C4").getValue();
  var line3 = nome3 + "\t\t" + fuel3 + "\t\t" + "Em: " + data3;

  var nome4 = sheet.getRange("A5").getValue();
  var fuel4 = sheet.getRange("D5").getValue();
  var data4 = sheet.getRange("C5").getValue();
  var line4 = nome4 + "\t" + fuel4 + "\t\t" + "Em: " + data4;

  var nome5 = sheet.getRange("A6").getValue(); //cBranco
  var fuel5 = sheet.getRange("D6").getValue();
  var data5 = sheet.getRange("C6").getValue();
  var line5 = nome5 + "\t" + fuel5 + "\t\t" + "Em: " + data5;

  var nome6 = sheet.getRange("A7").getValue();
  var fuel6 = sheet.getRange("D7").getValue();
  var data6 = sheet.getRange("C7").getValue();
  var line6 = nome6 + "\t" + fuel6 + "\t\t" + "Em: " + data6;

  var nome7 = sheet.getRange("A8").getValue();
  var fuel7 = sheet.getRange("D8").getValue();
  var data7 = sheet.getRange("C8").getValue();
  var line7 = nome7 + "\t" + fuel7 + "\t\t" + "Em: " + data7;

  var nome8 = sheet.getRange("A9").getValue();
  var fuel8 = sheet.getRange("D9").getValue();
  var data8 = sheet.getRange("C9").getValue();
  var line8 = nome8 + "\t\t" + fuel8 + "\t\t" + "Em: " + data8;

  var nome9 = sheet.getRange("A10").getValue();
  var fuel9 = sheet.getRange("D10").getValue();
  var data9 = sheet.getRange("C10").getValue();
  var line9 = nome9 + "\t\t" + fuel9 + "\t" + "Em: " + data9;

  var nome10 = sheet.getRange("A11").getValue();
  var fuel10 = sheet.getRange("D11").getValue();
  var data10 = sheet.getRange("C11").getValue();
  var line10 = nome10 + "\t" + fuel10 + "\t\t" + "Em: " + data10;

  var nome11 = sheet.getRange("A12").getValue(); //fZezere
  var fuel11 = sheet.getRange("D12").getValue();
  var data11 = sheet.getRange("C12").getValue();
  var line11 = nome11 + "\t" + fuel11 + "\t\t" + "N.A.";

  var nome12 = sheet.getRange("A13").getValue();
  var fuel12 = sheet.getRange("D13").getValue();
  var data12 = sheet.getRange("C13").getValue();
  var line12 = nome12 + "\t" + fuel12 + "\t\t" + "Em: " + data12;

  var nome13 = sheet.getRange("A14").getValue();
  var fuel13 = sheet.getRange("D14").getValue();
  var data13 = sheet.getRange("C14").getValue();
  var line13 = nome13 + "\t\t" + fuel13 + "\t\t" + "Em: " + data13;

  var nome14 = sheet.getRange("A15").getValue();
  var fuel14 = sheet.getRange("D15").getValue();
  var data14 = sheet.getRange("C15").getValue();
  var line14 = nome14 + "\t\t" + fuel14 + "\t\t" + "Em: " + data14;

  var nome15 = sheet.getRange("A16").getValue();
  var fuel15 = sheet.getRange("D16").getValue();
  var data15 = sheet.getRange("C16").getValue();
  var line15 = nome15 + "\t" + fuel15 + "\t\t" + "Em: " + data15;

  var nome16 = sheet.getRange("A17").getValue(); //ourique
  var fuel16 = sheet.getRange("D17").getValue();
  var data16 = sheet.getRange("C17").getValue();
  var line16 = nome16 + "\t" + fuel16 + "\t\t" + "Em: " + data16;

  var nome17 = sheet.getRange("A18").getValue();
  var fuel17 = sheet.getRange("D18").getValue();
  var data17 = sheet.getRange("C18").getValue();
  var line17 = nome17 + "\t" + fuel17 + "\t\t" + "Em: " + data17;

  var nome18 = sheet.getRange("A19").getValue();
  var fuel18 = sheet.getRange("D19").getValue();
  var data18 = sheet.getRange("C19").getValue();
  var line18 = nome18 + "\t" + fuel18 + "\t\t" + "Em: " + data18;

  var nome19 = sheet.getRange("A20").getValue();
  var fuel19 = sheet.getRange("D20").getValue();
  var data19 = sheet.getRange("C20").getValue();
  var line19 = nome19 + "\t\t" + fuel19 + "\t\t" + "Em: " + data19;

  var nome20 = sheet.getRange("A21").getValue();
  var fuel20 = sheet.getRange("D21").getValue();
  var data20 = sheet.getRange("C21").getValue();
  var line20 = nome20 + "\t" + fuel20 + "\t\t" + "Em: " + data20;

  var nome21 = sheet.getRange("A22").getValue();
  var fuel21 = sheet.getRange("D22").getValue();
  var data21 = sheet.getRange("C22").getValue();
  var line21 = nome21 + "\t" + fuel21 + "\t\t" + "Em: " + data21;

  var nome22 = sheet.getRange("A23").getValue();
  var fuel22 = sheet.getRange("D23").getValue();
  var data22 = sheet.getRange("C23").getValue();
  var line22 = nome22 + "\t\t" + fuel22 + "\t\t" + "Em: " + data22;

  var nome23 = sheet.getRange("A24").getValue();
  var fuel23 = sheet.getRange("D24").getValue();
  var data23 = sheet.getRange("C24").getValue();
  var line23 = nome23 + "\t" + fuel23 + "\t\t" + "N.A.";

  var nome24 = sheet.getRange("A25").getValue();
  var fuel24 = sheet.getRange("D25").getValue();
  var data24 = sheet.getRange("C25").getValue();
  var line24 = nome24 + "\t" + fuel24 + "\t\t" + "N.A.";

  var data = Utilities.formatDate(new Date(), "GMT", "dd-MMM-yyyy")

  var msg = ""+line0+"\n"+line1+"\n"+line2+"\n"+line3+"\n"+line4+"\n"+line5+"\n"+line6+"\n"+line7+"\n"+line8+"\n"+line9+"\n"+line10+"\n"+line11+"\n"+line12+"\n"+line13+"\n"+line14+"\n"+line15+"\n"+line16+"\n"+line17+"\n"+line18+"\n"+line19+"\n"+line20+"\n"+line21+"\n"+line22+"\n"+line23+"\n"+line24+" \n \nEMAIL GERADO AUTOMATICAMENTE POR GROUND OPERATIONS"

  MailApp.sendEmail("emails@gmail.com",  "Fuel "+data+"", msg, {
                    name: 'Fuel Qty Auto Message'});
}
\$\endgroup\$
5
\$\begingroup\$

You're in desperate need of a loop. Anytime you start numbering your variables, you should consider whether a loop would be appropriate. I'm sure you did, but you probably got hung up because your lineX assignments vary by just a little. That can be taken care of with a switch statement.

I'll start by defining some constants* for the different string values we have. That way we wont' mess anything up and they'll be consistent.

var TAB = "\t";
var DOUBLETAB = tab + tab;
var EM = "Em: ";
var NA = "N.A.";

Next we'll define a couple of simple functions that return cell values based on the row number we pass in as an argument.

function getNom(rowNumber) {
    return SpreadsheetApp.getActiveSheet().getRange(rowNumber,1).getValue();
}

function getFuel(rowNumber) {
    return SpreadsheetApp.getActiveSheet().getRange(rowNumber,4).getValue();
}

function getData(rowNumber) {
    return SpreadsheetApp.getActiveSheet().getRange(rowNumber,3).getValue();
}

Now we can start rewriting the main function with that loop and switch I was talking about earlier. But before I do, I'd like to note that you have a deceiving variable name.

var sheet = SpreadsheetApp.getActiveSpreadsheet();

This returns a Spreadsheet object, not a sheet object. Google uses ss ("spreadsheet") in their documentation, so i will follow suit and rename it appropriately.

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheets()[0]);

Ok, now that for loop...

function sendFuelcount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheets()[0]);

  var TAB = "\t";
  var DOUBLETAB = tab + tab;
  var NEWLINE = "\n"
  var EM = "Em: ";
  var NA = "N.A.";

  var line;

  for (var i = 1; i <= 25; i++) {
    switch(i) {
        case 1: 
            line = getNom(i) + DOUBLETAB + getFuel(i);
            break;
        case 2:
        case 5:
        case 6:
        case 7:
        case 8:
        case 11:
        case 12:
        case 13:
        case 16:
        case 17:
        case 18:
        case 19:
        case 21:
        case 22:
            line = line + NEWLINE + getNom(i) + TAB + getFuel(i) + DOUBLETAB + EM + getData(i);
            break;
        case 3:
        case 4:
        case 9:
        case 10:
        case 14:
        case 15:
        case 20:
        case 23:
            line = line + NEWLINE + getNom(i) + DOUBLETAB + getFuel(i) + DOUBLETAB + EM + getData(i);
            break;
        case 24:
        case 25:
            line = line + NEWLINE + getNom(i) + TAB + getFuel(i) + DOUBLETAB + NA;
            break;
    }

    var today = Utilities.formatDate(new Date(), "GMT", "dd-MMM-yyyy");
    var msg = line + NEWLINE + " " + NEWLINE + "EMAIL GERADO AUTOMATICAMENTE POR GROUND OPERATIONS";

    MailApp.sendEmail("emails@gmail.com",  "Fuel " + today + "", msg, {name: 'Fuel Qty Auto Message'});
  }

Note that you had var data instead of var date. I changed it to today so it won't be confused with the Date() function by Mr. Maintainer.

*Javascript doesn't actually have constants, but by convention, you shouldn't assign new values to variables with ALLCAPS names.

\$\endgroup\$
  • \$\begingroup\$ I still have to learn a lot! But I think I understand the basics of what you made there. Also that switch statement comes handy when dealing with different outputs. Thank you a lot. Hope this is useful to others. \$\endgroup\$ – user3817505 Jul 16 '14 at 17:01
  • \$\begingroup\$ Any particular questions about what I did @user3817505? \$\endgroup\$ – RubberDuck Jul 16 '14 at 17:26
  • \$\begingroup\$ For the moment no. But I will explore further and see if I get it. This will solve other problems in other projects too. Really cleaver way of dealing with tabulations and lines. Thank you my friend. \$\endgroup\$ – user3817505 Jul 16 '14 at 18:45
  • \$\begingroup\$ Where should I put the three functions you declare at the beggining? \$\endgroup\$ – user3817505 Jul 24 '14 at 9:24
  • \$\begingroup\$ Anywhere in the same file. \$\endgroup\$ – RubberDuck Jul 24 '14 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.