Google表单数据流转至独立工作表及后续处理方案技术咨询

Hey there, let's walk through building this full Google Sheets workflow step by step— I've implemented something almost identical for a project recently, so this should work smoothly for you:

First, let's assume your raw form submissions live in a sheet named Form Responses 1, and you want to sync them to a second sheet called Processed Data.

In cell A1 of Processed Data, paste this QUERY function to pull in all new submissions automatically:

=QUERY('Form Responses 1'!A:Z, "SELECT * WHERE A IS NOT NULL", 1)

• 'Form Responses 1'!A:Z targets all columns in your raw responses sheet (adjust the range if you don't need all columns)
• WHERE A IS NOT NULL filters out empty rows (column A is the default timestamp from Google Forms, so it'll never be empty for valid submissions)
• The final 1 tells Sheets your raw data has a header row, so it'll carry over column names too

This will update in real-time— every time someone submits the form, the data will pop up in Processed Data right away.

Google Forms dumps a combined timestamp (like 2024-05-20 14:45:30) into column A of Form Responses 1, which syncs to column A of Processed Data. Let's split this into two clean columns:

1. In cell B1 of Processed Data, type Date (your header)
2. In cell B2, use this ARRAYFORMULA to auto-generate dates for all rows:

=ARRAYFORMULA(IF(A2:A="", "", TEXT(A2:A, "YYYY-MM-DD")))

3. In cell C1, type Time (your header)
4. In cell C2, use this ARRAYFORMULA for times:

=ARRAYFORMULA(IF(A2:A="", "", TEXT(A2:A, "HH:MM:SS")))

• ARRAYFORMULA makes the formula apply to every row automatically, so you don't have to drag it down for new submissions
• Adjust the TEXT format (e.g., MM/DD/YYYY for US dates) if you need a different style

Now let's create a unique ID tied to the submission date (so IDs like SUB-20240520-001 for the first submission on May 20th, SUB-20240520-002 for the second, etc.):

1. In cell D1 of Processed Data, type Unique ID (your header)
2. In cell D2, paste this ARRAYFORMULA:

=ARRAYFORMULA(IF(A2:A="", "", "SUB-" & TEXT(A2:A, "YYYYMMDD") & "-" & TEXT(COUNTIFS(TEXT(A2:A, "YYYYMMDD"), TEXT(A2:A, "YYYYMMDD"), ROW(A2:A), "<="&ROW(A2:A)), "000")))

Here's what this does:

• SUB- is a custom prefix (you can change this to whatever you want, like REQ- or FORM-)
• TEXT(A2:A, "YYYYMMDD") converts the timestamp to a compact date string
• COUNTIFS(...) counts how many submissions happened on the same date up to the current row, then TEXT(..., "000") pads the number with leading zeros to keep IDs consistent (3 digits here, adjust to 00 or 0000 if you expect more submissions per day)

• If the QUERY function isn't pulling data: Double-check the sheet name 'Form Responses 1' matches exactly (including spaces and capitalization)
• If unique IDs are repeating: Make sure the COUNTIFS range uses relative references correctly (ROW(A2:A), "<="&ROW(A2:A) ensures it only counts rows above or equal to the current one)
• If date/time formatting looks off: Tweak the TEXT function's format code to match your locale (e.g., DD/MM/YYYY for European dates)

内容的提问来源于stack exchange，提问作者Joe Who