Here is a script to clear the status columns on an edit or the use of an Edit URL.

[Joe Schmidt]

I finally created a video to document a script to delete the status columns when a row is edited in the sheet or via an Edit URL. 

This script is for when you want to allow Autocrat to run when there is a change to any data in the sheet.  A new Output file will be created with the newest data.  This script will not delete the previous file created by Autocrat.

Thanks to Nolan Green for sharing the script.

The script will only clear the status information.  If the change is because of an Edit URL and there is a Form Trigger, then Autocrat should start automatically.  

If the script is triggered by an edit inside the spreadsheet, the status columns will be cleared but Autocrat will not run immediately.  

Video explanation of script.

Folder containing the files used in the video.

The script:

// Nolan Green shared this script with me. You need to change the columns and which sheet you

// want the script to use.  See below.

//

//THIS IS THE BEGINNING OF THE SCRIPT THAT CLEARS FIELDS WHEN A ROW IS EDITED.

function onEdit(e) {

  var currentSheet = e.range.getSheet();

  var sheetEdited = currentSheet.getName();

  var rowEdited = e.range.getRow();

  var columnEdited = e.range.getColumn();

  

  

  // column number where data should be cleared; A = 1, B = 2 etc

  // change the sheet name (sheetEdited) as appropriate. 

  if(sheetEdited == "Form Responses 1" && ( columnEdited < 5  || columnEdited > 8)){

    currentSheet.getRange(rowEdited,5).setValue('');

    currentSheet.getRange(rowEdited,6).setValue('');

    currentSheet.getRange(rowEdited,7).setValue('');

    currentSheet.getRange(rowEdited,8).setValue('');

  }

}

//THIS IS THE END OF THE SCRIPT THAT CLEARS THE AUTOCRAT FIELDS WHEN THE RESPONSES ARE EDITED

[Wim van Koppenhagen]

Thanks for this script! Very useful.

Op dinsdag 9 april 2019 00:59:00 UTC+2 schreef Joe Schmidt:

I finally created a video to document a script to delete the status columns when a row is edited in the sheet or via an Edit URL. 

This script is for when you want to allow Autocrat to run when there is a change to any data in the sheet.  A new Output file will be created with the newest data.  This script will not delete the previous file created by Autocrat.

Thanks to Nolan Green for sharing the script.

[Peter Brunet]

Hello Joe,

This script is exactly what I need for a job/sheet I am running; however, I am receiving an error:

Missing ) after argument list. (line 2, file "Code")

-Peter

[Mike Kegler]

This is super helpful, thank you. Can somebody explain the logic of Autocrat creating a new, separate document in this situation instead of overwriting the previous version?  I have a lot of use cases for this script, but it will result in a lot of files that look like duplicates (at first glance).

Thanks,

-Mike

[Joseph Schmidt]

Mike,

It is funny that you asked about all of the files.  Together with Peter Brunet, I additional lines of code that will read the file ID and then move the file to trash before it deletes the cells.

I got the idea to add the code while in a hangout with Jennifer Scott and Clay Smith.  Peter stuck with me and tested the code until we got it working.  And we can't forget Nolan Green who shared the script with me.  I hope I didn't forget anyone.  

I find it amazing that folks from around the world can learn and share.

Here is the current code.  It should also be in the documentation at the links I shared previously.  I may have to improve the documentation.

// Nolan Green shared this script with me. You need to change the columns and which sheet you
// want the script to use.  See below.
//
//THIS IS THE BEGINNING OF THE SCRIPT THAT CLEARS FIELDS WHEN A ROW IS EDITED.
function onEdit(e) {
  var currentSheet = e.range.getSheet();
  var sheetEdited = currentSheet.getName();
  var rowEdited = e.range.getRow();
  var columnEdited = e.range.getColumn();
 
 
  // column number where data should be cleared; A = 1, B = 2 etc
  // change the sheet name (sheetEdited) as appropriate.

  if(sheetEdited == "Form Responses 1" && ( columnEdited < 29  || columnEdited > 32)){
   
    // Add // in front of the next four lines if you don't want to delete the file created by Autocrat.
    // Make sure the output folder is shared with edit rights. Anyone who has the link can edit
    // Make sure to change the column number.
    // This code is new and experimental. I make adjustments as I learn.
   
   var sheetId = currentSheet.getRange(rowEdited,29).getValues();
   if (sheetId != "") {
         DriveApp.getFileById(sheetId).setTrashed(true);
    }
   
    currentSheet.getRange(rowEdited,29).setValue('');
    currentSheet.getRange(rowEdited,30).setValue('');
    currentSheet.getRange(rowEdited,31).setValue('');
    currentSheet.getRange(rowEdited,32).setValue('');

  }
}
//THIS IS THE END OF THE SCRIPT THAT CLEARS THE AUTOCRAT FIELDS WHEN THE RESPONSES ARE EDITED 

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/6a9dd32c-a487-4481-88aa-9e45a6b18986%40googlegroups.com.

[Mike Kegler]

Hi Joe,

Thanks for the additional information. That sounds like exactly what I was looking for.  Apologies if I missed it in the code.

All the best,

-Mike

You received this message because you are subscribed to a topic in the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nv-autocrat-add-on/sokUD5tY1OE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTOTuSfPZopW4e1tO-0xUpb7ddxOX3safA8SGReQ0o3Ww3Q%40mail.gmail.com.

[Joseph Schmidt]

Mike,

It was just added in the last couple of days.  Peter just told me that it was working for him.  Let me know if you have any issues.  The script isn't very fancy but it works.

Sometimes, I have more thoughts bouncing around in my head than I can handle.  Does that ever happen to anyone?  I'm sure it does.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CA%2B1NOy5xSqUM5aYp7%2Bm-SADR4d2PMBdwK_SQZebfvJVJHSoPbA%40mail.gmail.com.

[SD]

Thank you for providing this and the video! I'm getting this error code: TypeError: Cannot read property "range" from undefined. (line 2, file "Code").

Any ideas why?

On Monday, April 8, 2019 at 5:59:00 PM UTC-5, Joe Schmidt wrote:

[Peter Brunet]

Hello,

I have had a similar error when the end date was before the start date.

--

You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/0e27b977-23e0-43a4-8bcc-0909f868052a%40googlegroups.com.

--

Peter Brunet
Tech Integration Specialist
Leipzig International School gGmbH

Könneritzstr. 47

04229 Leipzig

Germany

 

Tel: +49 (0) 341 337 558-0

Fax: +49 (0) 341 337 558-96

www.ibrunet.com

[Joseph Schmidt]

This is not a script to be run via the normal means.  You set on or more triggers that cause the script to run.  The script checks the column that is changed to determine if it is a column that should cause the status information to be cleared and the file deleted.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPCrnfKMcwxAh%3DitaKMsy6_e_F8CB-CkUn7KbJovsuRMSvih6A%40mail.gmail.com.

[Peter Brunet]

Hello Joe,

This is Peter Brunet again.  You helped me tweak the following script below.  The highlighted RED is receiving an error after hitting the RUN.  However, the system still works.  Maybe b/c the trigger (edit function) is still listed under the CURRENT PROJECT'S TRIGGERS section.  This could be why it is working.  However, I am trying to duplicate this for another job and hitting RUN creates the error.

Any reasons you can think of.   I did get this message from Google : 

Enable new Apps Script runtime powered by Chrome V8 for this project.

More infoEnableDismiss   What is V8, besides the drink or motor; could this be the source of error?

Regards,

Peter B

// Nolan Green shared this script with me. You need to change the columns and which sheet you
// want the script to use.  See below.
//
//THIS IS THE BEGINNING OF THE SCRIPT THAT CLEARS FIELDS WHEN A ROW IS EDITED.
function onEdit(e) {
  var currentSheet = e.range.getSheet();
  var sheetEdited = currentSheet.getName();  
  var rowEdited = e.range.getRow();
  var columnEdited = e.range.getColumn();
 
 
  // column number where data should be cleared; A = 1, B = 2 etc
  // change the sheet name (sheetEdited) as appropriate.

  if(sheetEdited == "Form Responses 1" && ( columnEdited < 29  || columnEdited > 32)){
   
    // Add // in front of the next four lines if you don't want to delete the file created by Autocrat.
    // Make sure the output folder is shared with edit rights. Anyone who has the link can edit
    // Make sure to change the column number.

    // This code is new and experimental. I make adjustments as I learn.
   
   var sheetId = currentSheet.getRange(rowEdited,29).getValues();

   if (sheetId != "") {
         DriveApp.getFileById(sheetId).setTrashed(true);
    }
   
    currentSheet.getRange(rowEdited,29).setValue('');
    currentSheet.getRange(rowEdited,30).setValue('');
    currentSheet.getRange(rowEdited,31).setValue('');

    currentSheet.getRange(rowEdited,32).setValue('');

  }
}
//THIS IS THE END OF THE SCRIPT THAT CLEARS THE AUTOCRAT FIELDS WHEN THE RESPONSES ARE EDITED 

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTOQcn97rh0BML-zwVb3T2A4v%2BL%3DNZWnz2PbB4HVF8wrJvA%40mail.gmail.com.

[Joseph Schmidt]

This script is called from the Edit Trigger.  It will not run as an independent script.  It needs to know which row was edited which it gets from the value pasted to the script.

You test my making an edit.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPCrnfKm5_gaxEpW8UYZnfq1c%3DxVXjtji3sxsSQNzvfGxsXKRA%40mail.gmail.com.

[Mike Kegler]

For my exact use case, I changed it to on form submit. The script wasn't reading form resubmissions as edits, and my users won't be interacting directly in the spreadsheet.

Thanks,

-Mike

You received this message because you are subscribed to a topic in the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nv-autocrat-add-on/sokUD5tY1OE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTORD_m8J%3DdU1BTz-pUoY65bi8voVrVPcvDOLyyeUq%3D2UnA%40mail.gmail.com.

[Joseph Schmidt]

I usually set the run triggers for both conditions.  It should work but only when the action takes place.  You can't test the script by running it.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CA%2B1NOy4-2nTdH815uSJK3xQZ47%3DbbHfvDMOfQibECxY3h48Geg%40mail.gmail.com.

[Rob Fulcher]

Hi Joseph

Firstly, thanks for your video and script, it got me very excited!! 

This past year I have been trying to get lots of different elements of my business to run automatically, which has lead me to spending quite a lot of time going around in circles on google sheets, Google Forms and Zapier.

This is my issue...

Using Autocrat to fill a Slide Template to create a "certificate of inspection" for my customers, with information from a spreadsheet, the CRM platform Im using doesn't allow me to export address information on a line by line basis, so I have set up a Zap to take the customer name and update it into the first row of "Get Info" spreadsheet, which has a VLOOKUP formula on it which is linked to another worksheet which has all my customer database in and pulls all the information I need into the "Get Info" spreadsheet, if I then run autocrat it takes that information and creates my certificate and emails it etc etc, that all works fine.

So I used your script to delete the merge information upon an edit of the first column, which works if I physically edit it within the spreadsheet but the edit is happening via Zapier, and it doesn't seem to see that as an edit, I also tried to change the trigger to "on change" instead but that doesn't work either.

I saw the option for "on form submission" so I created a Google Form, with one question, sent the answer via a Zapier zap but the only option I have to to create new row, I cant update the row I need to.

So very frustrating, as I am so close.

I would be really grateful for your input.

The reason I used your script in the first place was because I tried copy the VLOOKUP formulas down the entire 1000 rows of the spread sheet, hoping that with each Zap update it would fill in the blank rows and fill the rows to the right as we went alone but it didn't, it update the row 1001, under all the blank ones with the formulas in.

Anyway I hope that all makes sense.

Look forward to hopefully hearing from you.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/0e27b977-23e0-43a4-8bcc-0909f868052a%40googlegroups.com.

--

Peter Brunet
Tech Integration Specialist
Leipzig International School gGmbH

Könneritzstr. 47

04229 Leipzig

Germany

 

Tel: +49 (0) 341 337 558-0

Fax: +49 (0) 341 337 558-96

www.ibrunet.com

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPCrnfKMcwxAh%3DitaKMsy6_e_F8CB-CkUn7KbJovsuRMSvih6A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTOQcn97rh0BML-zwVb3T2A4v%2BL%3DNZWnz2PbB4HVF8wrJvA%40mail.gmail.com.

--

Peter Brunet
Tech Integration Specialist
Leipzig International School gGmbH

Könneritzstr. 47

04229 Leipzig

Germany

 

Tel: +49 (0) 341 337 558-0

Fax: +49 (0) 341 337 558-96

www.ibrunet.com

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPCrnfKm5_gaxEpW8UYZnfq1c%3DxVXjtji3sxsSQNzvfGxsXKRA%40mail.gmail.com.

--
You received this message because you are subscribed to a topic in the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nv-autocrat-add-on/sokUD5tY1OE/unsubscribe.

To unsubscribe from this group and all its topics, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTORD_m8J%3DdU1BTz-pUoY65bi8voVrVPcvDOLyyeUq%3D2UnA%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

[Ricardo Fahrig]

Rob, why to update the existing row and not keep adding new rows for each client? 

The solution here is to VLOOKUP in an array formula instead of dragging it down, this way it applies to every new row added.

1. Zapier adds a new row for the client
2. Array vlookup loads the customer data
3. autoCrat with time trigger creates the document and mails it to the client 

I hope this helps,

Ricardo

[robfulcherflameskill]

Ricardo

Yea, that's what I have now managed to do, not opened a spread sheet in some time, very rusty but managed to sort everything this morning and have a system up and running now.

Thank you 

Kind regards

Rob Fulcher

Mobile: 07849349911

Email: rob.f...@flameskill.co.uk 

Flameskill Legal Disclaimer: This email is confidential and may contain legally privileged information. If you are not the intended recipient, you must not disclose or use the information contained in it. If you have received this email in error, please notify us immediately by return email and delete the document. You should protect your system from viruses, etc; Flameskill accepts no responsibility whatsoever for damage that may be caused by them.

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/7cba9529-2fa3-4da8-bf5c-5c10e2d4d8e6%40googlegroups.com.