4

I can calculate a date countdown pretty easily in sheets with a formula like this:

=MINUS(DATE(2015,5,25),TODAY())

Is there any way to add a date countdown in a Google Document?

If not, how about copying calculated data from a cell in sheets to a Google document?

  • 1
    Hey JJ and welcome to Web Applications. I saw your post already on the Google+ community and thus removed the hash-tags !! Stick-a-round as it might take a bit. – Jacob Jan Tuinstra May 22 '15 at 14:14
  • 1
    Side remark: in a spreadsheet I would use DATEDIF which offers more flexibility in formatting. Concerning your question: should be possible with a script that runs every day, grabs values from the spreadsheet and inserts into the document. I'll look into it later. – user79865 May 22 '15 at 15:20
2

I couldn't find a way to make a calculated date field, or link to a single Sheets field, so this is my closest solution for you.

Huge reference props to Ryan Heitner's answer here: https://stackoverflow.com/questions/13585272/insert-date-time-in-google-document

I've modified it to create a menu in your Google doc with one item to insert a string containing the # of full days left, with a description following it "X full days left!"

To set it up, in a google doc, go to Tools -> Script editor (if you don't have scripts already, you'll get a div, otherwise you get taken to a new tab asking which script to edit, or create a new one) and create a new blank project.

Put the below script in and save it whatever name you want. Then refresh or load your google doc and you should have the new menu item.

NOTE: This script is actually set for an end date of May 31, 2015 (var dtEnd = new Date(2015, 4, 31);) rather than the date you had. Adjust as needed, but remember the month is -1 of the actual month number since they're 0-based in this javascript-based scripting.

function onOpen() {
  var ui = DocumentApp.getUi();
  // Or FormApp or SpreadsheetApp.
  ui.createMenu('DateCountdown')
      .addItem('Insert Date Countdown', 'insertDateCountdown')
      .addToUi();

}

function insertDateCountdown() {
  var cursor = DocumentApp.getActiveDocument().getCursor();
  if (cursor) {
      // Attempt to insert text at the cursor position. If insertion returns null,
      // then the cursor's containing element doesn't allow text insertions.
      var d = new Date();
      var dtEnd = new Date(2015, 4, 31); //ADJUST MONTH by 1; month is 0-based!!
      // The Times are in milliseconds, and 86,400,000 is ms in a day
      var dayCount = Math.floor((dtEnd.getTime() - d.getTime()) / 86400000);
      var element = cursor.insertText(dayCount + " full days left!");
      if (element) {
        element.setBold(true);
      } else {
        DocumentApp.getUi().alert('Cannot insert text at this cursor location.');
      }
    } else {
      DocumentApp.getUi().alert('Cannot find a cursor in the document.');
  }

}
function pad (str, max) {
  str = str.toString();
  return str.length < max ? pad("0" + str, max) : str;
}

The script editor option:
enter image description here

The new menu item:
enter image description here

| improve this answer | |

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.