CODEWITHSUNDEEP
google-apps-script
TaylerQ
TaylerQ

Reputation: 15

How do I limit the days when a formula can run?

I am creating a google sheet that tracks word count in a google document each day. I already have found a way to pull word count into the spreadsheet. Right now I have each day set up like this: 

E2=IF(A2=TODAY(), J2,)

This works, but once the word count is updated, every cell with this formula updates. However, I need this formula to only run on the date specified so I can see what the word count was for that day, and be able to copy it down to the next line to run only on the following day, and so on. I know there isn't a way to do this with a regular formula, so I need a script to make it work. Can anyone help me out?

Upvotes: 1

Views: 49

Answers (1)

Joshua Dawson
Joshua Dawson

Reputation: 629

You might find it easier to have a sheet dedicated to storing the word counts found each day. Here is some code to help with what you need. All you need to do is:

  1. Fill out the spreadsheetId, sheetName, and documentId
  2. Set an installable trigger to run logMyWordCounts every day

.

/**
 * This is the function to use for the installable trigger
 */
function logMyWordCounts() {
  var spreadsheetId = "", // spreadsheet that contains log
      sheetName = "", // sheet to log to
      documentId = ""; // document to count words from

  var wordCount = getWordCount(documentId);

  logWordCount(spreadsheetId, sheetName, wordCount);
}

/**
 * Returns the word count of the document with the given id
 */
function getWordCount(documentId) {
  var text= DocumentApp.openById(documentId).getBody().getText();
  text = text.split(/\s+/);
  return text[0] === "" ? 0 : text.length;
}

/**
 * Returns a string in the format of "m/d/year"
 */
function formatDate(date) {
  return (date.getMonth()+1) + "/" + date.getDate() + "/" + date.getYear();
}

/**
 * Appends a row with the current date and given word count
 * to the sheet with the given name and spreadsheet id
 */
function logWordCount(spreadsheetId, sheetName, wordCount) {
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);

  sheet.appendRow([formatDate(new Date()), wordCount]);
}

Upvotes: 1

Related Questions