Как легко скопировать столбец с одного листа на другой в Google Sheets
programming automation google-apps-script

Как легко скопировать столбец с одного листа на другой в Google Sheets

27 апреля 2023 г.

В этом блоге я поделюсь скриптом, который позволит вам легко выбрать столбец в электронной таблице Google Sheets и скопировать его в другую таблицу, гарантируя, что значения выровнены в одних и тех же строках. Являетесь ли вы разработчиком скриптов Google Apps или просто хотите упростить свой рабочий процесс, этот скрипт будет вам полезен.

Ссылка на столбцы перед копированием

Копирование столбца с одного листа на другой может оказаться сложной задачей, если у нас нет уникального столбца в качестве ссылки для помещения значений в правильный столбец. Мы можем использовать такие столбцы, как идентификатор, адрес электронной почты или любой другой столбец, который существует на обоих листах, в качестве ссылки. В этой статье мы создадим электронную таблицу с двумя листами и используем столбец «ID» в качестве ссылки для копирования столбца «Имя» из Листа1 в . Лист2.

Вы можете просто скачать и использовать это лист или создайте свой собственный. Вы можете либо следовать инструкциям, либо загрузить полный код из репозитория git . .

Редактор сценариев

Теперь это связанный скрипт. Итак, откройте электронную таблицу, которую вы хотите использовать для этого проекта, и оттуда откройте редактор скриптов.

Open Script Editor

HTML-форма

Для этого проекта, в отличие от предыдущий, мы будем использовать настраиваемые диалоговые окна с HTML вместо подсказок пользовательского интерфейса, потому что для этого проекта нам потребуется несколько полей ввода.

Создайте новый файл HTML в редакторе скриптов. Затем скопируйте и вставьте следующие коды

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <!-- <style>
    .targetColumnInfo {
      color: grey;
      font-size: 10px;
    }
  </style> -->
</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>
    <!-- <p class="targetColumnInfo">*Leave Last Input Empty to create new target column</p> -->

    <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>

Обратите внимание на два исполнителя сценариев.

  1. withSuccessHandler() используется для связывания функции в форме на переднем плане. -end для функции в коде бэкенда (сценария) во время успешной операции, в данном случае setTargetColumns() — это используемая нами бэкэнд-функция.
  2. close(), который используется для закрытия диалогового окна. Мы используем его с withSuccessHandler() для вызова серверной части и закрытия диалогового окна.

При отображении форма выглядит следующим образом:

Copy Column HTML Form

==Вы можете изменить стиль HTML-формы, чтобы сделать ее более красивой.==

В этой HTML-форме вам нужно будет указать шесть элементов информации:

  1. Имя исходного рабочего листа.
  2. Имя столбца ссылок в исходном листе.
  3. Имя исходного столбца в исходном листе, значение которого необходимо скопировать.
  4. Имя целевого рабочего листа, куда должно быть скопировано значение.
  5. Имя ссылочного столбца на целевом листе.
  6. Имя целевого столбца на целевом листе, в котором будет храниться скопированное значение.

Копировать столбец на другой лист

Теперь давайте напишем скрипт для копирования значений из одного столбца в другой.

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
 */

Теперь аналогично предыдущим блогам. , теперь вы можете просто:

  1. Сохраните код.
  2. Обновите таблицу. Где вы увидите пользовательское меню, как показано ниже.
  3. И выполнить функцию.

После этого вы увидите меню, подобное изображенному ниже в электронной таблице.

Copy The Column To Sheet menu

Ошибка

Если вы оставите какое-либо поле ввода пустым или дали неверные имена полям, вы увидите предупреждение об ошибке, как на изображении ниже.

Error Alert

Спасибо за потраченное время

Меня зовут Нибеш Хадка. Я независимый эксперт по автоматизации и специализируюсь на автоматизации продуктов Google с помощью Apps Script. Итак, приступим! Если вам нужны мои услуги, дайте мне знать .

Не забудьте поставить лайк и поделиться этим блогом.

:::информация Также опубликовано здесь.

:::

Оригинал

Recent Post

Categories

PREVIOUS ARTICLE
NEXT ARTICLE