Form Trigger When Merging on Sheet Other Than Form Responses 1

[Joel Render]

Hi,

I'm having an issue with the Form Trigger not working on my merge job. As a little background, I was told by another Autocrat user that the Sheet that the job is merging from does not have to be the Form Responses 1 sheet. The reason that I did not want it to be on the Form Responses 1 sheet is that I've got some columns where I manually entered formulas to help facilitate parts of the merge process, and I know that they would get wiped out on the Form Responses 1 sheet as soon as I logged out of the sheet and came back in. The other user suggested that the other sheet simply query the Form Responses 1, which I have done. I have then set up the job to run from this second sheet, and I know that the job works fine when I run it manually. However, the job does not appear to be running on the Form Trigger because when I completed the Form, the merge job never ran.

Does anyone have any information or ideas regarding whether doing it this way negates the Form Trigger? If it does, are there other ways/formulas that would allow the information from Form Responses 1 to get to this second sheet for the merge and would still "trip" the Form Trigger?

By the way, I already read on here that some people who've had issues with the Form Trigger not running are advised to try turning the triggers off and then back on and also changing the time zone of the sheet to something else and then changing it back. I've already tried both of these to no avail.

Also, if it would be helpful, here is my User Key: AMWRuYh5Zib45jGgt/mMbc244tbWVfU5FHbLqYfEs1VwE/bA3N/Fb4DUF/9vtPHPdyfJeEGTU9CA

I appreciate any help that you can give me. Thanks for your time.

Joel

[Joseph Schmidt]

I recommend running on the Response sheet.  That doesn't mean that others have not gotten Autocrat to run on other sheets.

You are depending on the importrange to move the data.  I'm confused about formulas being deleted.  A formula should stay in the sheet.

Most folks either use an arrayformula to have the formula work on all rows in the response sheet or use the CopyDown add-on.  

I prefer using arrayformulas.  When using arrayformulas, I recommend deleting the empty rows in the response sheet.

=ArrayFormula(if(row(A:A)=1,"column title",if(E:E="Value",H:H,F:F&", "&G:G)))  

The above arrayformula in row one will add the title "column title" for row one and evaluate the IF statement to put either what is in column H or columns F & G separated by a space.  

You may want to study the arrayformula or the CopyDown Add-on to see which one you want to use.  There are pluses and minuses to each.

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/de63e731-6236-4243-ad88-f9228fe0ff82%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Joel Render]

First, I'm not sure if this is how you read it but to clarify when I say the same sheet I mean a different sheet (tab) in the same Google Sheets file not an entirely different file.

The issue with the formulas is that in the past on Form Responses sheets when I add columns and put in formulas, everything looks fine until I close the file. When I then reopen the file later, some (if not all) of the columns that I created are gone. Granted it's been a bit of time since I've had that happen, and I haven't tested it lately, so if Google has fixed that, then great!. 

However, even if they did fix it, the other reason I don't want to use the Form Responses sheet is that I want the information from the form to be formatted into an easily readable database. You can't really do that on the Form Responses sheet for two reasons (both of these I know are currently happening): 1. If you put any formatting into the cells on the Form Responses sheet and then close the file, when you reopen it, all of the formatting is reset back to the defaults, and 2. When a new form submission comes into the Form Responses sheet it ignores the formatting that is already in place for the cells and just formats the cells using all of the format defaults, which means Arial Size 10, no Bold, Underline, or Italics, no cell border, no cell color, etc. These are all things I want to use to format the readable database.

With all that said, running the Autocrat job from the Forms Responses 1 sheet is a last resort for me. Until then the issue is getting the Run on Form Trigger to work on the other sheet that does have all of the formatting that I have selected. As I mentioned, this sheet is querying the relevant responses from the Form Responses 1 sheet, and when the form is completed, it successfully goes from the Form Responses 1 sheet to the other sheet. When that happens it just doesn't seem to trip the Run on Form Trigger. I have tested the merge manually, and it is successful, so all of the information in the job seems to be set up correctly. I've also tried several other things without any success, such as changing the time zone of the spreadsheet to something else and then changing it back to the correct one (a recommendation that others on the forum have suggested), deleting the job and then making a brand new one, and removing Autocrat and then reinstalling it. None of them had any effect.

Does anyone else have any ideas?