How to write google sheet script so only latest timestamped forms record if duplicate is the only record?

simpler · December 15, 2019, 8:54pm

Hi, is there a way to delete all duplicates except the most recent timestamped dupe via google sheets app script? This is so the calcs are using recent numbers for that form record.

Muhammad_iqbal · December 16, 2019, 12:51pm

Is ‘=unique’ function not enough?

Jeff_Hager · December 23, 2019, 9:33pm

I would probably use a query against the sheet with a MAX and a GROUP BY.

simpler · December 30, 2019, 1:48am

Hi Jeff

Thanks for the offers of help! I managed to create my 1st google app script thanks to another post. It needs to automate and keep the google rows slim.

jake_z · December 30, 2019, 6:34am

How are you getting around the API limitations whereby the edit or change triggers don’t fire unless edited by a human? I was trying to use custom scripting in a sheet for some things but discovered the scripts will not fire if the sheet is edited via API as Glide does.

Carlos_DeSilva · December 30, 2019, 7:37am

Not entirely true. You can use the OnChange installable trigger. Anytime that Glide changes the data, the trigger fires allowing anything in the script to be run. I have such an example with one of my apps.

jake_z · December 30, 2019, 7:56am

Does it work?! According to google’s documentation, “Script executions and API requests do not cause triggers to run.”

https://developers.google.com/apps-script/guides/triggers/installable

simpler · December 30, 2019, 8:14am

Carlos is right.

jake_z · December 30, 2019, 8:38am

Does onChange fire only when the structure changes (i.e. row or col is added as the google docs imply)? Or does it also fire when a row is edited or deleted/cleared out from a user removing that item in glide?

Carlos_DeSilva · December 30, 2019, 3:06pm

Yes. You can even capture the type of change by using the “(e)” event for the trigger, i.e., “e.changetype” to see if it was an edit or something else.

Look at this… https://developers.google.com/apps-script/guides/triggers/events

Specifically,
changeType The type of change ( EDIT , INSERT_ROW , INSERT_COLUMN , REMOVE_ROW , REMOVE_COLUMN , INSERT_GRID , REMOVE_GRID , FORMAT , or OTHER ).

The syntax that you use in your script to figure out what type of change it is, is
‘if (e.changeType == EDIT){condition if true}’

The OnEdit trigger does not seem to work, unless someone has found a way to make it work with Glide. So, use the OnChange trigger and see how you can make it work for you.

D.shaman · August 7, 2021, 5:23pm

Hi, I know this is much later, but were you able to get the onChange function to run just once?
I am getting 5 duplicates when it is triggered…

Hope this finds you well, many thanks.

Michael

Darren_Murphy · August 7, 2021, 5:26pm

onChange will fire every time a change is made to the Google Sheet. You need to build logic into your trigger script to deal with that.

D.shaman · August 7, 2021, 5:51pm

Thank you for the reply.

I’ve tried setting locks and delays, but to no avail. I’ve even tried setting booleans like button states in hardware…

var state = false;

function onChange(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = sheet.getActiveRange();
  var row = activeRange.getRow();
  var column = activeRange.getColumn();
  Logger.log(JSON.stringify(e));

  if (row > 1 && column >=1 && column<=10 && state == false) {
    //If rowID IS NOT present -> createEvent()
    if(sheet.getRange(row,9).getValue()==""){
      createEvents(row);
      state = true;
      delay(10000);
      Logger.log("after delay");
      state = false;
    }
  } else {
  Logger.log('row: ' + row);
  Logger.log('column: ' + column);

  }
}

function createEvents(rNum){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current Events");
  var cal = CalendarApp.getCalendarById("57dldedl5niaauush3sg3m9td0@group.calendar.google.com");
  var title = ss.getRange(rNum, 2).getValue();
  var sdate = ss.getRange(rNum,5).getValue();
  var edate = ss.getRange(rNum,6).getValue();
  var desc = ss.getRange(rNum,7).getValue();
  //var desc = "Project: "+ss.getRange(2,1).getValue() + "\nEvent: " + ss.getRange(2,2).getValue()+ "\nValue: " + ss.getRange(2,3).getValue()+ "\nEffort: " + ss.getRange(2,4).   getValue()+ "\nNotes: " + ss.getRange(2,7).getValue()+ "\nActual Value: " + ss.getRange(2,8).getValue();

    cal.createEvent(title,sdate,edate,{description: desc}).setTimeZone('America/Chicago');

}

Any help would be appreciated.

Darren_Murphy · August 8, 2021, 2:05am

Try adding getRange(row,9).setValue("Anything that isn't an empty string") immediately before the call to createEvents(row);

Other observations/suggestions:

Using getActiveSheet() in an onChange trigger seems a bit odd to me. Unless you only have a single sheet in your workbook, how do you know that you’re operating on the correct sheet?
What I normally do with onChange triggers is use a wrapper function that first checks which sheet triggered the change, and then short circuit if it isn’t the one I’m interested in. So maybe something like this:

function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();

  if (sheetname == 'Sheet 1') {
    do_something();
  }
  else if (sheetname == 'Sheet 2') {
    do_something_else();
  }
  else if (sheetname == 'Sheet 3') {
    do_some_different_things();
  }
}

Hard-coded column numbers will always come back and bite you one day, I try to avoid them as much as possible. One way to avoid them is to leverage the indexOf() array method. eg:

var header = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();  // Returns the header row
var title_index = headers.indexOf('Title');  // returns the index of the "Title" column

Then in your getRange() calls you can refer to columns by name, instead of using “magic numbers”…

var title = ss.getRange(row,title_index+1).getValue();  // NB. JS Arrays are zero based, so you need to add one to the index when using with getRange() calls

D.shaman · August 9, 2021, 5:31pm

Thank you for your reply.

I’ll continue working on this problem!

D.shaman · September 1, 2021, 7:02pm

Hello, thank you so much for your input.

I’ve implemented all of the changes you suggest and I am still getting multiple instances.

Could you help me some more please?

Log

2021-09-01 14:54:12:554 -0400 000509 INFO hi from oSC
2021-09-01 14:54:13:004 -0400 000947 INFO hi from oSC
2021-09-01 14:54:13:044 -0400 001018 INFO hi from oSC
2021-09-01 14:54:13:140 -0400 001036 INFO hi from oSC
2021-09-01 14:54:13:550 -0400 001529 INFO header: Project,Event,Value,Effort,Start,End,Notes,Actual Value,Event ID,Action,Select Date Range,
2021-09-01 14:54:13:618 -0400 001561 INFO header: Project,Event,Value,Effort,Start,End,Notes,Actual Value,Event ID,Action,Select Date Range,
2021-09-01 14:54:13:768 -0400 001646 INFO header: Project,Event,Value,Effort,Start,End,Notes,Actual Value,Event ID,Action,Select Date Range,
2021-09-01 14:54:13:749 -0400 001728 INFO 33
2021-09-01 14:54:14:051 -0400 001947 INFO header: Project,Event,Value,Effort,Start,End,Notes,Actual Value,Event ID,Action,Select Date Range,
2021-09-01 14:54:14:063 -0400 002042 INFO 1
2021-09-01 14:54:14:216 -0400 002171 INFO 33
2021-09-01 14:54:14:539 -0400 002518 INFO sheetname: Current Events
2021-09-01 14:54:14:703 -0400 002677 INFO 1
2021-09-01 14:54:14:822 -0400 002777 INFO 1
2021-09-01 14:54:15:098 -0400 003077 INFO onChange triggered in general
2021-09-01 14:54:15:886 -0400 003782 INFO sheetname: Current Events
2021-09-01 14:54:16:085 -0400 004028 INFO inside the row
2021-09-01 14:54:16:329 -0400 004280 INFO onChange triggered in general
2021-09-01 14:54:16:911 -0400 004854 INFO Range
2021-09-01 14:54:17:109 -0400 005064 INFO inside if uID is “”
2021-09-01 14:54:17:590 -0400 005575 INFO Range
2021-09-01 14:54:17:780 -0400 005731 INFO Range
2021-09-01 14:54:18:254 -0400 006209 INFO after boop
2021-09-01 14:54:18:272 -0400 006150 INFO inside if uID is “”
2021-09-01 14:54:18:535 -0400 006509 INFO inside if uID is “”
2021-09-01 14:54:19:018 -0400 006896 INFO after boop
2021-09-01 14:54:19:260 -0400 007211 INFO after boop

Code

function createEvents(rNum){
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current Events");
  var cal = CalendarApp.getCalendarById("57dldedl5niaauush3sg3m9td0@group.calendar.google.com");
   var header = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();  
  // returns the index of the "Title" column
  var event_index = header.indexOf('Event');
  var start_index = header.indexOf('Start');
  var end_index = header.indexOf('End');
  var notes_index = header.indexOf('Notes');

  var event = sheet.getRange(rNum,event_index+1).getValue();
  var sdate = sheet.getRange(rNum,start_index+1).getValue();
  var edate = sheet.getRange(rNum,end_index+1).getValue();
  var desc = sheet.getRange(rNum,notes_index+1).getValue();
  //var desc = "Project: "+ss.getRange(2,1).getValue() + "\nEvent: " + ss.getRange(2,2).getValue()+ "\nValue: " + ss.getRange(2,3).getValue()+ "\nEffort: " + ss.getRange(2,4).   getValue()+ "\nNotes: " + ss.getRange(2,7).getValue()+ "\nActual Value: " + ss.getRange(2,8).getValue();
    cal.createEvent(event,sdate,edate,{description: desc}).setTimeZone('America/Chicago');
}

function createSpreadsheetChangeTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onChange')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onChange()
    .create();
  }  



var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();

function on_sheet_change(e) {
  Logger.log('hi from oSC');
  // Gets sheets info
  var sheet = e.source.getActiveSheet();
  var sheetname = sheet.getName();

  // Handles rows and columns
  var activeRange = sheet.getActiveRange();
  var row = activeRange.getRow();
  var column = activeRange.getColumn();
  // Returns the header row
  var header = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();
  Logger.log('header: ' + header);
  // returns the index of the "Title" column
  var eventID_index = header.indexOf('Event ID');
  // NB. JS Arrays are zero based, so you need to add one to the index when using with getRange() calls

  // Logger.log('header: ' + header);
  Logger.log(row);
  Logger.log(column);
  Logger.log('sheetname: ' + sheetname);
  if (sheetname == 'Current Events') {
      Logger.log("onChange triggered in general");
      if (row > 1 && column >=1 && column<=10) {
      Logger.log('inside the row');
      Logger.log(activeRange);
       //If rowID IS NOT present -> createEvent
       if(sheet.getRange(row,eventID_index+1).getValue()==""){
        Logger.log('inside if uID is ""');
        sheet.getRange(row,eventID_index+1).setValue("boop");
        Logger.log('after boop');
        createEvents(row);
      }
    } else {
      Logger.log('else');
    }
  }
}


function onChange(e) {
  on_sheet_change(e);
}

D.shaman · October 9, 2021, 3:53pm

Hey y’all!

I fixed my multiple trigger firing issue by going to the Triggers tab and deleting my 30+ onEdit and onChange triggers that I had created

Thank you for all the help - esp Darren_Murphy!

Darren_Murphy · October 9, 2021, 4:08pm

haha, that would do it
And my apologies for not responding to your last question - I must have missed that one!

Topic		Replies	Views
Row Modified timestamp Ask for Help	14	1226	December 8, 2020
Google Script: How to spot new sheet entry via form button if it's an EDIT event, not INSERT_ROW? Ask for Help	8	1054	April 16, 2020
Trigger script onEdit event from App Ask for Help	15	4705	January 17, 2024
Insert file ID into sheet from PDF generation Ask for Help	3	370	April 18, 2021
Detect specific changed column in Google Sheet based on add row action button Ask for Help	9	1795	November 16, 2022

How to write google sheet script so only latest timestamped forms record if duplicate is the only record?

Related Topics