CODEWITHSUNDEEP
javascripthtmlgoogle-apps-script
Ramy CoreMT
Ramy CoreMT

Reputation: 83

appendRow Column Starting Position

I'm trying to add a automatic time stamp when i submit my form into google sheets, however the problem is that i want the timestamp in the first column of a row and then the rest of the data imputed to come afterwards. I cant seem to get my processForm function to begin in the second column and leave the first one alone. If i attempt to put an empty formObject under the ws.appendRow part it would overwrite my onEdit function and not allow the date to appear. Is there any way for me to begin appending my row starting in the second column instead of the first?

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index')
      .evaluate();
}

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function onEdit(event)
{ 
  var timezone = "GMT-5";
  var timestamp_format = "MM-dd-yyyy"; // Timestamp Format. 
  var updateColName = "Total Defects";
  var timeStampColName = "Date";
  var sheet = event.source.getSheetByName('Data'); //Name of the sheet where you want to run this script.


  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
  if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}


/* @Process Form */
function processForm(formObject) {
  var url = "LINK TO SPREADSHEET URL";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  
  ws.appendRow([
                formObject.data_1,
                formObject.data_2,
                formObject.data_3
]);
}

'''

Upvotes: 2

Views: 400

Answers (1)

dwmorrin
dwmorrin

Reputation: 2734

The code to create the timestamp stands alone and deserves it's own timestamp() function.

(Note: "GMT-5" does not observe daylight savings: is this correct for you? Consider using the provided getSpreadsheetTimeZone.)

Once timestamp() is extracted, you can insert it into your processForm function like this:

/** @returns {string} */
function timestamp() {
  var timezone = "GMT-5"; // consider using getSpreadsheetTimeZone()
  var timestamp_format = "MM-dd-yyyy";
  return Utilities.formatDate(new Date(), timezone, timestamp_format);
}

/* @Process Form */
function processForm(formObject) {
  var url = "LINK TO SPREADSHEET URL";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");

  ws.appendRow([
    timestamp(),
    formObject.data_1,
    formObject.data_2,
    formObject.data_3,
  ]);
}

Upvotes: 1

Related Questions