I been trying gogle app scripts earlier. But I always get this error
I have never seen this myself, but have seen that other people have got it.
Exactly when does it happen in the process ?
Have you authenticated the script to run on your account?
When u Click on Publish and select on Deploy as Web App
Then u select Anyone, Even Anonymous and Click on Update
U get a prompt to Review Permission
When u clik to review, u are asked to select email account
When I select my email account
That is when this ERROR comes up
Thats why I been AVOIDING Google App Scripts
- it is not a web app, just a script that runs on the spreadsheet. If you have a web app running there already you will need to create another project.
See here:
sometimes the only way to resolve is to start again from the beginning with a new spreadsheet and script project.....
I deleted the project, Which I was trying to publish as a web app.
Created a new project
Put ur code in it and saved it
I created a new project and shared it as Editor with another email id.
Logged on with the other id, and created a trigger for this Project(DeleteRows)
It has been created (Time Interval Based. Testing it Every Minute).
But it is not working.
I have checked the Trigger Executions and snapshot is attached
OK !!!
I Deleted this project too and created another projected, and started from scratch.
Though I did not create a new sheet.
This time too, I had to share the sheet with the other id to get it functional.
The trigger is FUNCTIONAL NOW as OnFormSubmit. Earlier it was not giving this option.
Instead of (2,3) I have made it (3,5)
and left it as >10
Awesome learning !!
Tx.
@TIMAI2
This Google app script u sent has been a life saver. I am new to this stuff, but this approach can help automate a hoard of stuff I been trying for quite some time.
Can something similar, automate the process of UPDATE an existing row ??
-
If a record by a user has been submitted, and another record by SAME user is uploaded, instead of getting a UNIQUE record by max(Timestamp), aim is to UPDATE the record, by ADDING up data in the other columns.
-
If a record by a user has been submitted, and another record by SAME user is uploaded, instead of getting a UNIQUE record by max(Timestamp), aim is to UPDATE the record, by REPLACING data in the other columns.
(If I am able to achieve a UPDATE record by REPLACEING previous data, then the requirement of getting the UNIQUE DISTINCT record is done away with and also the requirement of DELETE rows subsequently is NOT required. So when data is uploaded, it checks for Name, if Name exists, then REPLACE, else append row)
Trigger in both cases would by OnFormSubmit
You have been quiet, so I have surmised it is all working OK 
Now to your questions....
Modifying records submitted via google forms is OK, as long as you NEVER go back into the form and try to update responses there. That said, finding the correct record to UPDATE might be fun....
Could you please show (on a spreadsheet) what you want to happen e.g. existing record, new record, final result for both 1 & 2?
For me this coding and application development is a passion and a hobby. Professionally, I am a Colonel in the Army. So was busy with other work.
Will create a spreadsheet and upload sample data, instead of meddling with the spreadsheets in use.
Sample data
Situation 1
User has uploaded data, and wants to UPDATE by REPLACE, maybe due to wrong data having been uploaded earlier. So when New record is submitted, if Name exists, then PREVIOUS record is DELETED, else new row is appended
Situation 2
User has uploaded data, and wants to UPDATE by ADDING, maybe due to having missed out some data earlier. So when New record is submitted, if Name exists, then PREVIOUS record is UPDATED by SUM of ranhge of other columns, else new row is appended
Thanks, that makes sense. The logic would be:
- There will only ever be one (or no) entry per name.
- The default action would be REPLACE (all), unless one or more items in the original record are empty.
- The latest date/timestamp would be used.
I am a bit focused on another project at the moment, but will have a look at this later, when i have my google sheets "head" 
on
Roger, Thank you.
Try this, works for me with this data
The one script handles both REPLACE and UPDATE and should just leave alone if a new entry.
function updateReplace() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('UPDATE');
var rg = sh.getRange(2,1,sh.getLastRow() -1,6).getValues();
var lastEntry = rg[rg.length-1];
var prevEntry = '';
for ( var i =0; i<rg.length -1; ++i ) {
if ( lastEntry[1] == rg[i][1] ) {
prevEntry = parseInt(i);
var range = sh.getRange(prevEntry + 2,1,1,6);
// REPLACE
if ( rg[prevEntry][3] != "" && rg[prevEntry][4] != "" && rg[prevEntry][5] != "" ) {
range.setValues([lastEntry]);
sh.deleteRow(rg.length + 1);
//UPDATE
} else {
if ( rg[prevEntry][3] == "" ) {
range = sh.getRange(prevEntry + 2,4,1,1);
range.setValue([lastEntry[3]]);
range = sh.getRange(prevEntry + 2,1,1,1);
range.setValue([lastEntry[0]]);
}
if ( rg[prevEntry][4] == "" ) {
range = sh.getRange(prevEntry + 2,5,1,1);
range.setValue([lastEntry[4]]);
range = sh.getRange(prevEntry + 2,1,1,1);
range.setValue([lastEntry[0]]);
}
if ( rg[prevEntry][5] == "" ) {
range = sh.getRange(prevEntry + 2,6,1,1);
range.setValue([lastEntry[5]]);
range = sh.getRange(prevEntry + 2,1,1,1);
range.setValue([lastEntry[0]]);
}
sh.deleteRow(rg.length + 1);
}
}
}
}
Trigger is OnFormSubmit??
Because I copied this script and when data uploaded to sheet, the script gave an error
Error
Script function not found: onFormSubmit
Made 2 changes in the script
Working now
So its basically BOTH the functions COMBINED into ONE script !!
It is AMMENDING a value, if existing, and APPENDING a value if NULL
One Query !
Where do I make changes in the script? This sheet was a sample with test data. Actual Sheet has 35 Columns !!!
But Seriously, This Google App Script stuff is Awesome. Need to get to Learn this now.
Thank you for ur effort and time. Will check with live data and revert
I was worried about that
The script would need rewriting in order to iterate over the the list of columns data in each entry for the appending of values. A nasty, complicated thing! The replace function is not so bad, as we just check if all the columns have an entry.
It may be better to throw the problem back at the user....
Use the fill in form functionality to provide a filled in google form of their last return, and just replace the existing entry (much the same if using an app to complete the entry - return their last entry as response content).
You may also want to consider dropping the use of google forms altogether....
Dropping the use of Google Form ??
Means call Sheet id and Sheet Name from app and define functions in Google App Scripts and call them?
Havent tried it, but its been on my mind for quite a while.
Will give it a shot !
I have understood the logic inthis app script u sent.
I tried modifying it to another google sheet with different number of columns , and it worked.
SO I guess, I wud be able to do it for the 35 Column sheet too.
Thank you for ur efforts and time.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.