Automatically Move Data from Google Form to Another Spreadsheet Every Week

Suppose you receive data from a Google Form once per week, and you need to copy and paste it into another table with a different order or formatting.

You can create an Apps Script that is triggered either by a specific date or whenever new data arrives from the Google Form. The script can then copy the data you select, format it in a certain way, and move it to the desired table. Does that sound good?

This is an Apps Script code you can use, and below the code, I've pointed out what you need to customize to make it work in your case.

Apps Script Code

1. Open your Google Sheet.

2. Go to Extensions > Apps Script.

3. Delete any existing code and paste the following script:

   function moveToNewTable() {
     // Get the active sheet
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1"); // Change to your sheet name
     const data = sheet.getDataRange().getValues();
   
     // Assuming the "Name" column is the second column (index 1)
     const nameColumnIndex = 1;
   
     // Iterate through each row starting from the second row (skipping headers)
     for (let i = 1; i < data.length; i++) {
       const row = data[i];
       const name = row[nameColumnIndex];
   
       // Check if a sheet for this name already exists, if not, create it
       let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
       if (!targetSheet) {
         targetSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(name);
       }
   
       // Append the row to the target sheet
       targetSheet.appendRow(row);
     }
   }

Here are the necessary elements you need to set in the script for it to work:

1. Spreadsheet Name for the Source Data
   Replace "Form Responses 1" with the actual name of the sheet containing your Google Form responses.

   const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Your_Sheet_Name");

2. Column Index for the "Name" Field
   Verify that nameColumnIndex points to the correct column for "Name" (index starts at 0). If "Name" is the second column, leave as 1. Adjust if needed.

   const nameColumnIndex = 1;  // Change if "Name" is in a different column

How this script works

1. Accessing the Active Spreadsheet and Sheet
   The script begins by accessing the active Google Spreadsheet and selecting the sheet named "Form Responses 1". This is where the form responses or main data are stored.

   const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

2. Retrieving All Data
   The script retrieves all the data from the selected sheet as a 2D array, where each row represents a form response, and each cell contains the value from a specific column.

   const data = sheet.getDataRange().getValues();

3. Identifying the "Name" Column
   It assumes the "Name" column is the second column in the sheet (index 1), which will be used to categorize each row by the person's name.

   const nameColumnIndex = 1;

4. Looping Through Each Row
   The function loops through each row in the data array, starting from the second row (index 1), to skip the header row. For each row, it extracts the name from the "Name" column.

   for (let i = 1; i < data.length; i++) {
     const row = data[i];
     const name = row[nameColumnIndex];

5. Checking if a Sheet Exists for Each Name
   For each row, the script checks if a sheet with the respondent's name (value in the "Name" column) already exists:

   • If a sheet with that name already exists, it sets it as the target sheet.
   • If it does not exist, it creates a new sheet with that name.

   let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
   if (!targetSheet) {
     targetSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(name);
   }

6. Appending Data to the Target Sheet
   The script appends the entire row of data to the corresponding target sheet for that name.

   targetSheet.appendRow(row);

This function takes each row of data from the main sheet ("Form Responses 1") and:

• Looks at the "Name" column to determine which sheet to move the data to.
• Checks if a sheet for that specific name already exists. If not, it creates a new sheet.
• Copies the row of data into the relevant sheet, organizing the data by name automatically.

Each time this function is run, the data will be split across sheets named after each unique entry in the "Name" column, helping to keep responses organized by individual.

Setting Up Triggers

Choose when this data should be moved to another table automatically.

1. Time-Based Trigger

This trigger will run the function moveToNewTable at regular time intervals (e.g., every hour or day).

1. In the Apps Script editor, go to Triggers (clock icon in the left toolbar).
2. Click on + Add Trigger in the lower right corner.
3. In the trigger setup window:
   • Choose which function to run: Select moveToNewTable.
   • Choose which deployment should run: Select the current deployment (usually “Head”).
   • Select event source: Choose Time-driven.
   • Select type of time based trigger: Choose your desired interval, such as Hour timer or Day timer.
   • Select time interval: Choose a specific interval, like every hour or every day.
4. Click Save to set the trigger.

With this trigger, moveToNewTable will run at the specified time intervals, ensuring data is moved regularly.

2. Trigger Based on New Data (Form Submission)

This trigger will run moveToNewTable whenever new data is added to the sheet by a form submission.

1. In the Apps Script editor, go to Triggers (clock icon in the left toolbar).
2. Click on + Add Trigger in the lower right corner.
3. In the trigger setup window:
   • Choose which function to run: Select moveToNewTable.
   • Choose which deployment should run: Select the current deployment (usually “Head”).
   • Select event source: Choose From spreadsheet.
   • Select event type: Choose On form submit.
4. Click Save to set the trigger.

This trigger will automatically move data to separate sheets based on names each time a new form submission is recorded in the main sheet.

Here is the guide how to apply this Google Apps Script

Now it's time to backup your data with makeCopy Google Apps Script

Summary

You now have two triggers:

1. A time-based trigger that runs moveToNewTable at a set interval (e.g., hourly).
2. A form submission trigger that moves data whenever a new response is submitted.

This setup ensures your data is always organized, either periodically or immediately upon form submission.