How to automatically remove the data from selected cells 30 minutes after last person use?

Question

I have a Google sheet where it's required to enter search data at daily basis.

For security purposes, i would like to automate the process of deleting the the individual search cell to be emptied or cleared 30 minutes from the last user.

Is there anyway on doing this? Looking forward to the guides given and many thanks.

I am new user for google sheet and not really get it familiar to google scrip. I had tried to set up a time based trigger but failed. Possibility with wrong function i had used.

Would like to ask for proper function if have.

Answer 1

I wrote this little script that, from my first tests, should do what you're looking for. It has to be bound to the spreadsheet (not a standalone script)

Note that you have to setup two triggers from the Apps Script IDE. Feel free to ask if you have questions but I added comments a bit everywhere to make it clearer.

const EDITED_RANGES_KEY = 'editedRanges'
const LAST_UPDATE_KEY = 'lastUpdate'

/*
This function will register all edits made by users and the time they made it.
1. Go to the 'triggers' section of Apps Script IDE
2. Add a new trigger of type 'everytime something change' (I don't know how it's written in english) and tell to call `registerEdits`
3. Validate the authorization scope (an pop-up will appear)
4. that's it
*/
function registerEdits(e) {
  // script properties to store data, available at anytime for the script
  console.log(e.source.getActiveSheet().getName(), e.range.getA1Notation())
  let scriptProperties = PropertiesService.getScriptProperties()
  let editedRanges = JSON.parse(scriptProperties.getProperty(EDITED_RANGES_KEY) || '[]') // we can't store arrays directly
  let fullRangeNotation = e.source.getActiveSheet().getName() + '!' + e.range.getA1Notation() 

  console.log('There are ' + editedRanges.length + ' ranges edited so far. Adding a new one: ' + fullRangeNotation)
  
  editedRanges.push(fullRangeNotation)
  scriptProperties.setProperty(EDITED_RANGES_KEY, JSON.stringify(editedRanges))
  
  let lastUpdateBeforeThisOne = scriptProperties.getProperty(LAST_UPDATE_KEY)
  
  if(lastUpdateBeforeThisOne) {
    let d = new Date()
    d.setTime(lastUpdateBeforeThisOne)
    console.log('Last update before this one was: ' + d)
  } else {
    console.log('There were no edit recorded before this one')
  }
  
  scriptProperties.setProperty(LAST_UPDATE_KEY, (new Date()).getTime()) // store the new timestamp, in ms
}

function getsheetNameFromNotation(notation) {
  return notation.slice(0, notation.indexOf('!'))
}

function getRangeFromNotation(notation) {
  return notation.slice(notation.indexOf('!')+1, notation.length)
}

/*
This function will get all the recorded edits and,
if the last of them (more recent) is olden than 30min,
it will clear all the cells impacted by those changes
in any sheet it might have happened
1. Go to the 'triggers' section of Apps Script IDE
2. Add a new trigger of type 'time driven' (I don't know how it's written in english) and tell to be run every 30min
3. Validate the authorization scope (an pop-up will appear)
4. that's it
*/
function every30min() {
  let scriptProperties = PropertiesService.getScriptProperties()
  let lastUpdate = scriptProperties.getProperty(LAST_UPDATE_KEY)
  if(lastUpdate == null) {
    console.log("No edit recorded...")
    return
  }
  const THIRTY_MIN_IN_MS = 30 * 60 * 1000
  let now = new Date()
  if(now.getTime() - lastUpdate >= THIRTY_MIN_IN_MS) {
    console.log('Something has been edited 30min ago or more')
    // retrieve all edited ranges
    let editedRanges = JSON.parse(scriptProperties.getProperty(EDITED_RANGES_KEY) || '[]')
    editedRanges.forEach(editedRange => {
      // don't forget the ranges are actually A1 notation with sheet name preprended (string) i.e. 'sheetName!cellRange'
      let sheetName = getsheetNameFromNotation(editedRange)
      let range = SpreadsheetApp.getActive().getSheetByName(sheetName).getRange(getRangeFromNotation(editedRange))
      range.clearContent(); // use `clear()` instead, if you also want to remove formatting
    })
    scriptProperties.deleteProperty(EDITED_RANGES_KEY)
    scriptProperties.deleteProperty(LAST_UPDATE_KEY)
    console.log('All content has been cleared')
  } else {
    console.log('Edits are too recent, not touching it...')
  }

}

If you want to read a bit of documentation (it's always better), the script uses the ScriptProperties to store data which is accessible throughout all trigger executions, it's basically a small database bound the spreadsheet. On every edit, it uses the data in the event object to know which cells of which sheet have been modified.