Google Sheets script to insert a timestamp on a row inserted from a form? How to detect the inserted row using onChange?

Question

I am attempting to write a script that will insert a timestamp on rows (in the 23rd column) inserted into a Google sheet from a form (not a google form; it is some other vendor that sends the data to the sheet and does not pass a timestamp).

I have been trying to script something from examples, but I cannot seem to get it to work. The script I have so far is as follows:

function setUpTrigger() {
  ScriptApp.newTrigger('timestamp')
  .forSpreadsheet('spreadsheet_id_goes_here')
  .onChange()
  .create();
}

function timestamp(e){

  if (e.changeType == 'INSERT_ROW'){
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(e.range.getRow(), 23).setValue(new Date());
  } 
}

Any help is greatly appreciated

Answer 1

You see e.range.getRow() method. so you have check row number comes is valid or not. otherwise, you have used sh.getLastRow() method to add a new record.

function timestamp(e){
  if(e.changeType == 'INSERT_ROW'){
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lastRow = sh.getLastRow()+1;
    sh.getRange(lastRow ,23).setValue(new Date);
 } 
}

Answer 2

According to the documentation for the onChange event object, e can contain information about the following:

• authMode - which is a value from the ScriptApp.AuthMode enum.;

• changeType - which represents the type of change (EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, or OTHER).;

• triggerUid - which is the ID of trigger that produced this event.;

• user - which represents a User object, representing the active user, if available.

Therefore, in order to insert the timestamp needed you will have to make use of one of the above options.

Reference

• Apps Script Event Objects.

Answer 3

I can't reproduce the behavior you see with the standard version of GNU make on my GNU/Linux system. Here is a complete repro case that I used:

$ cat main.h
#define RET 0

$ cat main.cpp
#include "main.h"
int main() { return RET; }

$ cat Makefile
CXX          := g++
CXXFLAGS     := -Wall -Werror -std=c++11
CPPFLAGS     := -I.
OBJDIR       := obj
DEPDIR       := $(OBJDIR)/.deps
DEPFLAGS      = -MT $@ -MMD -MP -MF $(DEPDIR)/$*.d

COMPILE.cc = $(CXX) $(DEPFLAGS) $(CXXFLAGS) $(CPPFLAGS) $(TARGET_ARCH) -c

SRCS = $(wildcard *.cpp)
OBJS = $(SRCS:%.cpp=$(OBJDIR)/%.o)
DEPS = $(SRCS:%.cpp=$(DEPDIR)/%.d)

all: main

$(OBJDIR)/%.o: %.cpp
$(OBJDIR)/%.o: %.cpp $(DEPDIR)/%.d | $(DEPDIR) ; $(COMPILE.cc) $(OUTPUT_OPTION) $<

$(DEPDIR): ; @mkdir -p $@

$(DEPS): ;

main: $(OBJS) ; $(CXX) $(CXXFLAGS) $(LDLIBS) $(LDFLAGS) -o $@ $^

clean: ; rm -rf obj main

-include $(wildcard $(DEPS))

$ make
g++ -MT obj/main.o -MMD -MP -MF obj/.deps/foo.d -Wall -Werror -std=c++11 -I.  -c -o obj/main.o main.cpp
g++ -Wall -Werror -std=c++11   -o main obj/main.o

$ make
make: Nothing to be done for 'all'.

Also something odd: the output of my make -p does not show any automatic variables, as are shown in your example:

obj/main.o: main.cpp obj/.deps/main.d main.cpp main.h | obj/.deps
#  Implicit rule search has been done.
#  Implicit/static pattern stem: 'main'
#  Last modified 2022-01-08 14:18:05.5745307
#  File has been updated.
#  Successfully updated.
#  recipe to execute (from 'Makefile', line 19):
    $(COMPILE.cc) $(OUTPUT_OPTION) $<

Maybe the version of GNU make you are using has some local changes?

If you download the vanilla GNU make source from https://ftp.gnu.org/gnu/make/ and compile that does it work properly?