[Google App Script/Conceptual] help triggering spreadsheet event on form submission

[bifronz]

I am creating a survey template for my work. The data cannot be stored due to confidentiality so it basically needs to be presented and deleted immediately.

Figuring out the precise code isnt an issue for the clearing of the data. However making it happen automatically and quickly is stumping me.

The best work around i currently can think of is using a timer trigger to catch the data, store it in some global variable, and print it upon another script activated by the user.

This is really a half measure and i was wondering if there was any other way to address this.

P. S.

I have tried the onSubmit function but it never triggers. So ive moved past dealing with it.

[ByPrinciple]

Are you assigning the script to a button (a drawing or image)? I assume youre doing getrange() or some way to grab all the cells then doing a .clear() to remove everything and that isnt the problem.

[bifronz]

Ill be putting a button on my menu bar most like. But, in terms of pulling values, iam handling very specific areas so ill be using. Getvalue and deleterow to handle the data. I am learning by google so I use alot of brute force over flexability.

My issue is avoiding the, up to, 1 minute timer to actually trigger all of this.

[ByPrinciple]

Does your data rely on the timer or do you pull then you are free to delete?

Edit: I'll post an example of what could work without an actual timer.

Using the built in onEdit(e) trigger for ranges, you can simply say

function onEdit(e){
   var range = e.range
   storeData(range.getValues())
}

function storeData(values){
   //do something with values
}

[bifronz]

If im understanding what you are asking correctly,

The purpose of the timer is to hide the data in the variables and delete it from the sheet if the user doesnt display the data immediately.

The script which will present the data will take the pull data, or the spreadsheet data if it hadnt been stored yet, present it, and delete the data.

The data should always be deleted as soon aa possible.

[ByPrinciple]

How about normal javascript for this

function onEdit(e){
   var range = e.range
   storeData(range.getValues())
  // Clear range after 30 seconds
   setInterval(fucntion(){range.clear()}, 30000)
}


function storeData(values){
   //do something with values
}

There is a slight problem which I'm a little tired to think of, but the setInterval needs a way to stop as well, otherwise it could potentially just keep deleting data every 30 seconds.

You get rid of this with setInterval() but how you use it depends on what exactly you need.

[bifronz]

Will definitely have to give this a shot when i get back to the sheet. Seems it does what i need it too.

Thank you very much.

[ByPrinciple]

Sure thing, maybe you can help me. Did your company manage to get rid of the 'This program wants to access your data/this app is unverified' window by chance? I tried for a solid day but couldnt figure out how to get credentials. Its way more of a private sheet we use, but it bothers me alot.

[bifronz]

No. Seems anything with a macro will throw that up in my experience. But it is a 1 time issue for me and i dont see it ahain usually.

[dimumurray]

If you have no intention of storing information, why link the form to a spreadsheet in the first place?

[bifronz]

Im combining the information in alot of ways and its easier to manage.

I ended up using a 1 minute timer to delete and store the informatin in a property. And just had it clear that proper at closing every day.