How To Move Rows of Data Between Google Sheets

Tutorial Category:

Share This:

Have you ever needed to move one row or multiple rows of data from one sheet to another within the same Google spreadsheet? With this tutorial, you’ll not only learn how to do that but also make sure the user is on the correct sheet when performing the move as well as selecting the correct number of columns. Let’s get started!

Let’s begin with the following spreadsheet example:

How To Move Rows of Data Between Google Sheets

  • There are three columns of data.
  • There are two sheets: Submitted and Approved.
  • We want to move rows of data from the Submitted sheet to the Approved sheet.

STEP ONE

  • Select the first row of data in the spreadsheet (not the column headings).
  • Navigate to Tools > Script Editor.
  • Google will ask you what type of project you want to create the script for. Choose Blank Project. Give your project a name.

STEP TWO

You’ll see a function was already created for you called myFunction(). Delete the whole function and replace it with:

function approveRequests() {
 var ss = SpreadsheetApp.getActiveSpreadsheet()
     sheet = ss.getActiveSheet(),
     sheetName = sheet.getName(),
     data = sheet.getDataRange().getValues();
 if (sheetName == "Submitted") {
  var range = sheet.getActiveRange(),
      startRow = range.getRowIndex(),
      numRows = range.getNumRows(),
      numCols = range.getNumColumns()
  if (numCols == 3) {
   if (data.length > 1) {
    var values = range.getValues(),
        nextSheet = ss.getSheetByName("Approved"),
        lastRow = nextSheet.getLastRow();
    nextSheet.getRange(lastRow+1,1,numRows,3).setValues(values);
    sheet.deleteRows(startRow,numRows);
   } 
  } 
 } 
}

Let’s explain what’s going on in that code.

  1. We get the active sheet’s name and all its data.
  2. If the active sheet’s name is Submitted, we then get the active range, meaning the range of cells currently selected by the user.
  3. We then get the starting row, the number of rows, and the number of columns for that selected range.
  4. If three columns are selected and there’s actually data on the sheet, we get the values in the selected range.
  5. We then get the Approved sheet and the last row on the Approved sheet.
  6. We get the next available row on the Approved sheet and set its range of data to the selected range from the Submitted sheet.
  7. Finally, we delete the selected rows from the Submitted sheet.

STEP THREE

How To Move Rows of Data Between Google Sheets 2

Now let’s run the code! Make sure the function approveRequests is selected. Then click Run, which looks like a play button.

STEP FOUR

You’ll be asked to authorize the script to run. This happens the first time you run a script. It will also be asked if you include any new calls to Google Apps Script APIs. Click Continue. Then click Accept.

What was the result?

How To Move Rows of Data Between Google Sheets 3

  • Switch back to your spreadsheet. The Submitted sheet should now have two rows of data.
  • The Approved sheet should have one row of data, the first row that was previously on the Submitted sheet.
  • You successfully moved a row of data from one sheet to another!

STEP FIVE

Now try this. Select only two columns of data on the Submitted sheet. Switch to the Script Editor and run the approveRequests function.

What was the result?

  • Switch back to your spreadsheet. The Submitted sheet still has two rows of data right?
  • That’s a good thing! Remember, we want the user to select ALL columns of data.
  • Because 3 columns were not selected, nothing was moved.

STEP SIX

Now try this. Switch to the Approved sheet and select the first row of data (not the column headings). Switch to the Script Editor and run the approveRequests function.

What was the result?

  • Switch back to your spreadsheet. The Approved sheet still has the one row of data right?
  • That’s a good thing! Remember, the user should only be able to move rows of data from the Submitted sheet to the Approved sheet.

You now have the ability to move rows of data from one sheet to another within a Google spreadsheet! To make it easier for the end user, you may want to display a message to them if they haven’t selected enough columns of data or are on the wrong sheet. You may also want to provide them with a custom menu so they can run the function using the menu instead of going into the script editor. Have fun!

Share This:

Related Tutorials