Recycling the form

Recyling the form saves time making the form, keeps all the data together in one spreadsheet, and best of all, saves you from having to recreate autoCrat!

You have 2 things, a Google form that someone submits, and a spreadsheet that stores all the responses.

Because we're using pre-filled links, we also have a sheet in our spreadsheet with our reliable information. That's 2 sheets, probably labeled "start here," or maybe "links," and another called "Form Responses."

We send out progress reports, etc., each quarter. Wouldn't it be nice if we didn't have to redo the pre-filled links and the form? And we wouldn't have to redo the autoCrat script?

Here's an easy way to do it:

  • When you're ready for 2nd Quarter, or any repeat report, go to the form.
  • Update the title if needed.
  • Click "responses." On top right, click the vertical ellipsis (we call them "skittles").
  • Unlink the form. Don't worry. All the responses are still stored in the spreadsheet that was just unlinked--still in your drive.
  • Now don't be frightened đź‘»! Delete the responses. This will remove them from the form, but not from the spreadsheet.
  • Select the "response destination" as the same sheet you just unlinked! This will create a new sheet in the spreadsheet, which you can label as "Q2" or something.
  • Now the responses for each quarter will be stored in the same spreadsheet, but on different pages.
  • Optional: Hide the sheet you don't want people to mess with. Click the tiny triangle next to the sheet's name at the bottom. In the menu that opens, choose "Hide sheet."
  • When you need to view the hidden sheet, just click View and choose Hidden sheets.

Unlink form

Hide sheet

View hidden sheet

But wait! There's more!

Yes, just one more step to complete the job of recycling your form/autoCrat.

On the spreadsheet that is now linked to the form, there are at least two sheets--the original sheet and one that will receive the responses from the recycled form.

Under the add-ons menu of the new sheet, open AutoCrat. Go to step 3, "Map Source Data to Template." Use the drop-down menu to change the "Merge tab" to the name of the new sheet.

I like to add a formula for the date =A2-INT(A2). I use copyDown to post that to each row.

This will require you to fix the first data row to row 5, not row 3.

Change "Merge tab" to the name of the new sheet.