In this blog, I will be sharing a script that allows you to easily select a column in a Google Sheets spreadsheet and copy it to another spreadsheet while ensuring that the values are aligned in the same rows. Whether you're a Google Apps Script Developer or just someone looking to streamline their workflow, this script will be a helpful tool for you. This is Nibesh Khadka. I am a freelance Google Apps Script Developer and Content Creator. Referencing Columns Before Copying Copying a column from one sheet to another can be challenging if we don't have a unique column as a reference to put the values in the correct column. We can use columns like ID, Email, or any other column that exists in both sheets as a reference. In this article, we'll create a spreadsheet with two sheets and use the "ID" column as a reference to copy the "Name" column from to . Sheet1 Sheet2 You can just download and use this sheet or create your own. You can either follow the steps or download the complete code from the git . repository Script Editor Now this is the bound script. So, open the spreadsheet you want to use for this project and open the script editor from there. HTML Form For this project, unlike in the one, we'll be using with HTML instead of UI prompts because we'll need multiple input boxes for this project. previous Custom Dialog Boxes Create a new file in your script editor. Then copy and paste the following codes HTML <!DOCTYPE html>
<html>
<head>
  <base target="_top">
  
</head>

<body>
  <form onsubmit="formSubmitHandler(event)">
    <h2>Source Sheet</h2>
    <label for="sourceSheet">Source Sheet Name:</label><br>
    <input type="text" id="sourceSheet" name="sourceSheet"><br>
    <label for="referenceColumn">Reference Column Name</label><br>
    <input type="text" id="referenceColumn" name="referenceColumn"><br>
    <label for="sourceColumn">Source Column To Copy From:</label><br>
    <input type="text" id="sourceColumn" name="sourceColumn"><br>
    <hr>
    <h2>Target Sheet</h2><br>
    <label for="targetSheet">Target Sheet Name:</label><br>
    <input type="text" id="targetSheet" name="targetSheet"><br>
    <label for="targetReferenceColumn">Reference Column Name</label><br>
    <input type="text" id="targetReferenceColumn" name="targetReferenceColumn"><br>
    <label for="targetColumn">Target Column To Copy To:*</label><br>
    <input type="text" id="targetColumn" name="targetColumn"><br>
    

    <input type="submit" value="Submit" />

  </form>

  <script>
    function formSubmitHandler(event) {

      const sourceSheet = event.target['sourceSheet'].value;
      const referenceColumn = event.target['referenceColumn'].value;
      const sourceColumn = event.target['sourceColumn'].value;
      const targetSheet = event.target['targetSheet'].value;
      const targetReferenceColumn = event.target['targetReferenceColumn'].value;
      const targetColumn = event.target['targetColumn'].value ?? "";
      const data = [sourceSheet, referenceColumn, sourceColumn, targetSheet, targetReferenceColumn, targetColumn];

      // call server side function
     google.script.run.withSuccessHandler(closeDialog).setTargetColumns(data);

      event.preventDefault();

    }

    function closeDialog() { google.script.host.close() }
  </script>
</body>
</html> There are two to notice here. script runners is used to link the function in the form in the front-end to the function in the backend(script) code during operation success, in this case, is the backend function we're using. withSuccessHandler() setTargetColumns() which is used to close the dialog box. We are using it with to call the backend and then close the dialog box. close() withSuccessHandler() When rendered the Form looks like this: Feel free to style the HTML Form to make it more beautiful. In this HTML form you'll have to provide six pieces of information: The name of the Source Worksheet. The name of the Reference Column in the source sheet. The name of the Source Column in the source sheet whose value is to be copied. The name of the Target Worksheet where the value is to be copied. The name of the Reference Column in the target sheet. The name of the Target Column in the target sheet, that will hold the copied value. Copy Column to Different Sheet Now let's write the script to copy the values from one column to another. function renderForm() {
  const html = HtmlService.createHtmlOutputFromFile('form')
    .setWidth(400)
    .setHeight(600);// play with dimension to fit your need
  SpreadsheetApp.getUi() 
    .showModalDialog(html, "Copy Column With Reference");
}

//["Sheet1", "ID", "Name", "Sheet2", "ID", "Name"]
function setTargetColumns(data) {
  try {
    const sourceSheetName = data[0];
    const referenceColumnName = data[1];
    const sourceColumnName = data[2];
    const targetSheetName = data[3];
    const targetReferenceColumnName = data[4];
    const targetColumnName = data[5];

    // get sheet and data
    const sheet = SpreadsheetApp.getActiveSpreadsheet();
    const sourceSheet = sheet.getSheetByName(sourceSheetName);

    const sourceSheetData = sourceSheet.getDataRange().getValues();
    const sourceSheetLastRow = sourceSheetData.length;
    const sourceSheetLastColumn = sourceSheetData[0].length;
    // get header column of source sheet
    const sourceSheetHeader = sourceSheet.getRange(1, 1, 1, sourceSheetLastColumn).getValues().flat();

    // find the index of the given column names
    const referenceColumnIndex = sourceSheetHeader.indexOf(referenceColumnName.trim());
    if (referenceColumnIndex === -1) throw "Reference Column Not Found";

    const sourceColumnIndex = sourceSheetHeader.indexOf(sourceColumnName.trim());
    if (sourceColumnIndex === -1) throw "Source Column Not Found"; // if the name is not found then throw error

    const sourceSheetData2 = [sourceSheet.getRange(2, referenceColumnIndex + 1, sourceSheetLastRow, 1).getValues().flat(), sourceSheet.getRange(2, sourceColumnIndex + 1, sourceSheetLastRow, 1).getValues().flat()]


    const targetSheet = sheet.getSheetByName(targetSheetName);
    const targetSheetData = targetSheet.getDataRange().getValues();
    const targetSheetLastRow = targetSheetData.length;


    const targetReferenceColumnIndex = sourceSheetHeader.indexOf(targetReferenceColumnName.trim());
    if (targetReferenceColumnIndex === -1) throw "Target Sheets Reference Column Not Found";

    const targetColumnIndex = sourceSheetHeader.indexOf(targetColumnName.trim());
    if (targetColumnIndex === -1) throw "Target Sheet's Target Column Not Found";

    const targetSheetRefData = targetSheet.getRange(2, targetReferenceColumnIndex + 1, targetSheetLastRow - 1, 1).getValues().flat();
    const targetSheetColData = targetSheet.getRange(2, targetColumnIndex + 1, targetSheetLastRow - 1, 1).getValues();


    for (let i = 0; i < sourceSheetData2[0].length; i++) {
      for (let j = 0; j < targetSheetRefData.length; j++) {
        if (targetSheetRefData[j] === sourceSheetData2[0][i]) {
          targetSheetColData[j] = [sourceSheetData2[1][i]];
          break;
        }
        continue;
      }
    }
    // set new values 
    targetSheet.getRange(2, targetColumnIndex + 1, targetSheetLastRow - 1, 1).setValues(targetSheetColData);

  } catch (e) {
    // alert error
    SpreadsheetApp.getUi().alert(`Error: ${e}`)
  }
}


/**
 * Menu creates menu UI in the document it's bound to.
 */
function createCustomMenu() {
  const menu = SpreadsheetApp.getUi().createMenu("Copy Columns");

  menu.addItem("Copy Column", "renderForm");
  menu.addToUi();
}


/**
 * OnOpen trigger that creates menu
 * @param {Dictionary} e
 */
function onOpen(e) {
  createCustomMenu();
}

/**
 * This script is for safely and accurately copying one column to 
 * another column in two sheets while properly aliging with other
 * rows.
 * It is created in such a way that it only works with bound script.
 *
 * Created by: Nibesh Khadka.
 * linkedin: https://www.linkedin.com/in/nibesh-khadka/
 * website: https://nibeshkhadka.com
 */ Now, similar to the , you can now just: previous blogs Save the code. Reload the spreadsheet. Where you'll see the custom menu as shown below And execute the function. After this, you'll see a menu like the image below in the spreadsheet. Error In case you leave any input box empty or you gave the wrong names to the boxes, you'll see the error alert like the image below. Thank You for Your Time My name is Nibesh Khadka, and as a freelance automation expert, I specialize in automating Google products with Apps Script. So let's get started! If you need my services let me . know Don’t forget to like and share this blog. Also published . here

Use This Script to Separate the First and Last names in Google Sheets

How to check word count in Google Docs?

Subscribe To Our News Letter For Free Contents

Hire Me

How to Copy a Column From One Sheet to Another in Google Sheets Easily

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Access User Location with Permission Handler in Flutter

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

12 Ways You Can Use Email to Nurture Leads to Conversion

Access User Location with Permission Handler in Flutter

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

12 Ways You Can Use Email to Nurture Leads to Conversion

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps