How to add date to empty cell by default if none specified in Google Form, on form submit with App Script?

0
votes

I have a time log form that has a date field I do not want to have to fill out every time as 90% of the time the date would be today's date. (Sometimes you will be entering something from another day and need to change it).

When an entry is added column A is always going to have something, so it should check to see if A is empty and if that same rows D is empty it will input today's date into D.

Some of the example's I have looked at were for onEdit of cell change it, but I could not get it to work with a form submit or even just have it automatically check all the cells in D and if empty put today's date (Only when B has value).

I have a function to sort the sheet right now that is called when on form submit/onEdit happens and would like to stick the new formula in the bottom of the same one (unless that is bad practice). The reason for this is less functions to have to add to the trigger list.

A: Timestamp | B: What | C: Paid? | D: Date

2
google-apps-scriptgoogle-sheetsgoogle-forms
Hi Davey, could you please add some code snippet relate to your description? – Eugene
Hey, I didn't post any related code as everything I had semi working felt wrong and did not want to confuse people. After work today I will try again and post some code. The basis of the function would be to have a function called from the Triggers onEdit, onFormSubmit (Just onFormSubmit might work now that I think about it) - is the cell in column D Empty && this rows cell in column A !empty? Then put todays date in column D's cell. As you can tell I can describe it, but still unable to write my own custom functions. Hopefully soon this will change – Davey

2 Answers

0
votes

You can try using Date from Google spreadsheets function list which Google Sheets offers.

And, as mentioned in Custom Functions in Google Sheets, if the available functions aren't enough for your needs, you can also use Google Apps Script to write custom functions then use them in Google Sheets just like a built-in function.

To get started, guidelines for custom functions and samples can be found in the given documentations.

0
votes

You can use Google Apps Script with onFormSubmit trigger which will populate the said cell with default date.

Here is a sample code which will populate the current date in Column 4 of the row in question.

function onFormSubmit(e) {
  //edit responses sheet name
  var responseSheetName = 'Form Responses 2';
  //Edit colmn number, column in which the date has to be autopopulated
  var column = 4;

  //Get target row number
  var row = e.range.rowStart;
  //If no date, pouplate the cell with current date
  if(!e.values[column-1]){
    SpreadsheetApp.getActive().getSheetByName(responseSheetName).getRange(row, column).setValue(new Date())
  }
}

In order to setup the above code, open the Spreadsheet which contains the form responses. Go Tools > Script Editor. This will open script editor window. Paste above code and edit the responseSheetName and column . Now Save it. After saving it, setup an on form submit trigger.

To setup the trigger, follow these steps.

  1. From the script editor, choose Resources > Current project's triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function onFormSubmit which you want to trigger.
  4. Under Events, select From spreadsheet.
  5. Select On form submit
  6. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
  7. Click Save.