Google-sheets – Save daily to the Gmail in Google Sheets

gmailgoogle sheetsgoogle-apps-script

Right now what I have is this in a Google sheet for my company daily lunch program:

function sendLunchPerkSummery() {
  var d = Date();
  var file = DriveApp.getFileById('1oOXv1PF9mL0pAUa_jWyac7lsLdWOAIVRpq49Hl1ioH4');  
  MailApp.sendEmail('xzy@gmail.com', 'LunchPerkRecord PDF', d, {
     name: 'Lunch Perk Emailer',
     attachments: [file.getAs(MimeType.PDF)]//, blob]

  });
}

This sends me a (daily) email making the sheet into a PDF and I have a record of it.

One problem: The formatting is HORRIBLE. It doesn’t look at all like my clean sheet. Rows are cut off and put on page 2-6 or the PDF. Is there a way I can get the formatting to look more like what I see in the sheet? WYSIWYG?

Best Answer

Rather than a "Mail Merge" solution (one document for each record in a list), one alternative that could work for you use is something like UltraDox that include a feature to insert a spreadsheet into a Google document.

I didn't tried it yet.

Related Solutions

Google Sheets – Daily Calculation and Record Insertion

With the following Google Apps Script you can grab the LIBOR value and added it to a new sheet. Goto the script editor:
enter image description here

and add this script:

UPDATE 25-01-2013
I've noticed that the trigger occasionally troughs a NA#. Most probably because the GoogleFinance formula takes some time to get its info. Therefore I revised the snippet a bit, to have a sleep function added to it:

NEW

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // sleep
  Utilities.sleep(6000);

  // get cell value
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

OLD

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // flush the spreadsheet before using it  
  SpreadsheetApp.flush();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get cell value from sheet[0]
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

The clue to the story is to create a time based trigger:
enter image description here

and set the trigger to execute at 5-6 pm:
enter image description here

See the example file I´ve created: LIBOR. I've added an extra menu item, so that you can execute the script this way.

Google-sheets – Daily running average in Google Sheets possible or not

If you have those tips down column A, rows 1-5, you could put the following in cell B1, or wherever you choose to have your running daily average field.

=SUM($A$1:A1)/ROWS($A$1:A1)

Then you will want to fill that formula (dragging the little + at the lower right corner of the B1 cell) down through the rest of your days. When you fill the formula, the anchored cell will remain for every formula, but each new row/day will add a new value and count into the calculation.

The resulting formulas should be:

=SUM($A$1:A1)/ROWS($A$1:A1)
=SUM($A$1:A2)/ROWS($A$1:A2)
=SUM($A$1:A3)/ROWS($A$1:A3)
=SUM($A$1:A4)/ROWS($A$1:A4)
=SUM($A$1:A5)/ROWS($A$1:A5)
...

and the resulting values for your daily tips:

34
67
51.6666666666667
46.25
41.8

Best Answer

Related Solutions

Google Sheets – Daily Calculation and Record Insertion

Google-sheets – Daily running average in Google Sheets possible or not

Related Topic