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

Уровень кодирования : Средний
Продолжительность : 30 минут
Тип проекта : Надстройка Google Workspace.

Цели

• Поймите, что делает решение.
• Узнайте, что делают службы Apps Script в решении.
• Настройте среду.
• Настройте сценарий.
• Запустите скрипт.

Об этом решении

Копирование макросов Google Sheets из одной таблицы в другую вручную может занять много времени и привести к ошибкам. Это дополнение Google Workspace автоматически копирует проект сценария и прикрепляет его к указанной пользователем электронной таблице. Хотя это решение ориентировано на макросы Таблиц, вы можете использовать его для копирования и совместного использования любого скрипта, привязанного к контейнеру.

Как это работает

Скрипт копирует проект Apps Script, привязанный к исходной электронной таблице, и создает дубликат проекта Apps Script, привязанный к указанной пользователем электронной таблице.

Службы сценариев приложений

В этом решении используются следующие сервисы:

• Служба URL-Fetch – подключается к API Apps Script для копирования исходного проекта и создания копии.
• Служба сценариев — авторизует API сценариев приложений, чтобы избежать повторного запроса авторизации.
• Служба электронных таблиц — открывает целевую электронную таблицу для добавления скопированного проекта Apps Script.
• Сервис карт – Создает пользовательский интерфейс дополнения.

Предварительные условия

Для использования этого образца необходимы следующие предварительные условия:

• Учетная запись Google (для учетных записей Google Workspace может потребоваться одобрение администратора).

• Веб-браузер с доступом в Интернет.

• Проект Google Cloud .

• API Google Apps Script включен в настройках панели управления Apps Script .

Настройте свою среду

Откройте свой облачный проект в консоли Google Cloud.

Если он еще не открыт, откройте проект Cloud, который вы собираетесь использовать для этого примера:

1. В консоли Google Cloud перейдите на страницу «Выбор проекта» .

   Выберите облачный проект

2. Выберите проект Google Cloud, который вы хотите использовать. Или нажмите «Создать проект» и следуйте инструкциям на экране. Если вы создаете проект Google Cloud, вам может потребоваться включить для него оплату .

Включите API сценариев Google Apps.

В этом кратком руководстве используется API сценариев Google Apps.

Прежде чем использовать API Google, вам необходимо включить их в проекте Google Cloud. Вы можете включить один или несколько API в одном проекте Google Cloud.

• В своем облачном проекте включите API сценариев Google Apps.

  Включите API

Настройка экрана согласия OAuth

Для надстроек Google Workspace требуется настройка экрана согласия. Настройка экрана согласия OAuth вашего дополнения определяет, что Google отображает пользователям.

1. В консоли Google Cloud выберите > API и службы > Экран согласия OAuth .

   Перейдите на экран согласия OAuth.

2. В качестве типа пользователя выберите «Внутренний» , затем нажмите « Создать» .
3. Заполните форму регистрации приложения, затем нажмите «Сохранить и продолжить» .

4. На данный момент вы можете пропустить добавление областей и нажать «Сохранить и продолжить» . В будущем, когда вы создадите приложение для использования за пределами вашей организации Google Workspace, вам необходимо изменить тип пользователя на Внешний , а затем добавить области авторизации, необходимые вашему приложению.

5. Просмотрите сводную информацию о регистрации приложения. Чтобы внести изменения, нажмите «Изменить» . Если регистрация приложения выглядит нормально, нажмите «Вернуться на панель управления» .

Настройте сценарий

Создайте проект скрипта приложений.

1. Нажмите следующую кнопку, чтобы открыть проект «Поделиться макросом Apps Script».
   Открыть проект
2. Нажмите Обзор .
3. На странице обзора нажмите Создать копию. .

Скопируйте номер облачного проекта

1. В консоли Google Cloud выберите > IAM и администрирование > Настройки .

   Перейдите в IAM и настройки администратора.

2. В поле Номер проекта скопируйте значение.

Установите облачный проект проекта Apps Script.

1. В скопированном проекте Apps Script нажмите «Настройки проекта». .
2. В разделе «Проект Google Cloud Platform (GCP)» нажмите «Изменить проект» .
3. В поле «Номер проекта GCP» вставьте номер проекта Google Cloud.
4. Нажмите Установить проект .

Установите тестовое развертывание

1. В скопированном проекте Apps Script нажмите « редактора» .
2. Откройте файл UI.gs и нажмите «Выполнить» . При появлении запроса авторизуйте сценарий.
3. Щелкните Развертывание > Тестовые развертывания .
4. Нажмите «Установить» > «Готово» .

Получите макроскрипт и информацию о электронной таблице.

1. Откройте таблицу Таблиц, в которой есть макрос и у вас есть разрешение на редактирование. Чтобы использовать образец электронной таблицы, сделайте копию образца таблицы макросов .
2. Нажмите Расширения > Скрипт приложений .
3. В проекте Apps Script нажмите «Настройки проекта». .
4. Под идентификатором сценария нажмите «Копировать» .
5. Отложите идентификатор сценария для использования на более позднем этапе.
6. Откройте или создайте новую таблицу, в которую вы хотите добавить макрос. У вас должно быть разрешение на редактирование таблицы.
7. Скопируйте URL-адрес электронной таблицы и отложите его для использования на следующем этапе.

Запустите сценарий

Убедитесь, что API сценариев Google Apps включен в настройках панели управления . Выполните действия, описанные в следующих разделах, чтобы запустить сценарий.

Скопируйте макрос

1. В Таблицах на правой боковой панели откройте надстройку «Поделиться макросом». .
2. В разделе «Исходный макрос» вставьте идентификатор сценария.
3. В разделе «Целевая таблица» вставьте URL-адрес таблицы.
4. Нажмите «Поделиться макросом» .
5. Нажмите Разрешить доступ и авторизуйте надстройку.
6. Повторите шаги 2–4.

Откройте скопированный макрос

1. Если он еще не открыт, откройте электронную таблицу, в которую вы скопировали макрос.
2. Нажмите Расширения > Скрипт приложений .
3. Если вы не видите скопированный проект Apps Script, убедитесь, что API Google Apps Script включен в настройках панели управления , и повторите шаги, перечисленные в разделе Копирование макроса .

Просмотрите код

Чтобы просмотреть код скрипта приложений для этого решения, нажмите «Просмотреть исходный код» ниже:

Посмотреть исходный код

Код.gs

решения/надстройка/share-macro/Code.js

Посмотреть на GitHub

// To learn how to use this script, refer to the documentation:
// https://developers.devsite.corp.google.com/apps-script/add-ons/share-macro

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Uses Apps Script API to copy source Apps Script project 
 * to destination Google Spreadsheet container.
 * 
 * @param {string} sourceScriptId - Script ID of the source project.
 * @param {string} targetSpreadsheetUrl - URL if the target spreadsheet.
 */
function shareMacro_(sourceScriptId, targetSpreadsheetUrl) {

  // Gets the source project content using the Apps Script API.
  const sourceProject = APPS_SCRIPT_API.get(sourceScriptId);
  const sourceFiles = APPS_SCRIPT_API.getContent(sourceScriptId);

  // Opens the target spreadsheet and gets its ID.
  const parentSSId = SpreadsheetApp.openByUrl(targetSpreadsheetUrl).getId();

  // Creates an Apps Script project that's bound to the target spreadsheet.
  const targetProjectObj = APPS_SCRIPT_API.create(sourceProject.title, parentSSId);

  // Updates the Apps Script project with the source project content.
  APPS_SCRIPT_API.updateContent(targetProjectObj.scriptId, sourceFiles);

}

/**
 * Function that encapsulates Apps Script API project manipulation. 
*/
const APPS_SCRIPT_API = {
  accessToken: ScriptApp.getOAuthToken(),

  /* APPS_SCRIPT_API.get
   * Gets Apps Script source project.
   * @param {string} scriptId - Script ID of the source project.
   * @return {Object} - JSON representation of source project.
   */
  get: function (scriptId) {
    const url = ('https://script.googleapis.com/v1/projects/' + scriptId);
    const options = {
      "method": 'get',
      "headers": {
        "Authorization": "Bearer " + this.accessToken
      },
      "muteHttpExceptions": true,
    };
    const res = UrlFetchApp.fetch(url, options);
    if (res.getResponseCode() == 200) {
      return JSON.parse(res);
    } else {
      console.log('An error occurred gettting the project details');
      console.log(res.getResponseCode());
      console.log(res.getContentText());
      console.log(res);
      return false;
    }
  },

  /* APPS_SCRIPT_API.create
   * Creates new Apps Script project in the target spreadsheet.
   * @param {string} title - Name of Apps Script project.
   * @param {string} parentId - Internal ID of target spreadsheet.
   * @return {Object} - JSON representation completed project creation.
   */
  create: function (title, parentId) {
    const url = 'https://script.googleapis.com/v1/projects';
    const options = {
      "headers": {
        "Authorization": "Bearer " + this.accessToken,
        "Content-Type": "application/json"
      },
      "muteHttpExceptions": true,
      "method": "POST",
      "payload": { "title": title }
    }
    if (parentId) {
      options.payload.parentId = parentId;
    }
    options.payload = JSON.stringify(options.payload);
    let res = UrlFetchApp.fetch(url, options);
    if (res.getResponseCode() == 200) {
      res = JSON.parse(res);
      return res;
    } else {
      console.log("An error occurred while creating the project");
      console.log(res.getResponseCode());
      console.log(res.getContentText());
      console.log(res);
      return false;
    }
  },
   /* APPS_SCRIPT_API.getContent
   * Gets the content of the source Apps Script project.
   * @param {string} scriptId - Script ID of the source project.
   * @return {Object} - JSON representation of Apps Script project content.
   */
   getContent: function (scriptId) {
    const url = "https://script.googleapis.com/v1/projects/" + scriptId + "/content";
    const options = {
      "method": 'get',
      "headers": {
        "Authorization": "Bearer " + this.accessToken
      },
      "muteHttpExceptions": true,
    };
    let res = UrlFetchApp.fetch(url, options);
    if (res.getResponseCode() == 200) {
      res = JSON.parse(res);
      return res['files'];
    } else {
      console.log('An error occurred obtaining the content from the source script');
      console.log(res.getResponseCode());
      console.log(res.getContentText());
      console.log(res);
      return false;
    }
  },

  /* APPS_SCRIPT_API.updateContent
   * Updates (copies) content from source to target Apps Script project.
   * @param {string} scriptId - Script ID of the source project.
   * @param {Object} files - JSON representation of Apps Script project content.
   * @return {boolean} - Result status of the function.
   */
  updateContent: function (scriptId, files) {
    const url = "https://script.googleapis.com/v1/projects/" + scriptId + "/content";
    const options = {
      "method": 'put',
      "headers": {
        "Authorization": "Bearer " + this.accessToken
      },
      "contentType": "application/json",
      "payload": JSON.stringify({ "files": files }),
      "muteHttpExceptions": true,
    };
    let res = UrlFetchApp.fetch(url, options);
    if (res.getResponseCode() == 200) {
      return true;
    } else {
      console.log(`An error occurred updating content of script ${scriptId}`);
      console.log(res.getResponseCode());
      console.log(res.getContentText());
      console.log(res);
      return false;
    }
  }
}

UI.gs

решения/надстройка/share-macro/UI.js

Посмотреть на GitHub

/**
 * Copyright 2022 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// Change application logo here (and in manifest) as desired.
const ADDON_LOGO = 'https://www.gstatic.com/images/branding/product/2x/apps_script_48dp.png';

/**
 * Callback function for rendering the main card.
 * @return {CardService.Card} The card to show the user.
 */
function onHomepage(e) {
  return createSelectionCard(e);
}

/**
 * Builds the primary card interface used to collect user inputs.
 * 
 * @param {Object} e - Add-on event object.
 * @param {string} sourceScriptId - Script ID of the source project.
 * @param {string} targetSpreadsheetUrl - URL of the target spreadsheet.
 * @param {string[]} errors - Array of error messages. 
 * 
 * @return {CardService.Card} The card to show to the user for inputs.
 */
function createSelectionCard(e, sourceScriptId, targetSpreadsheetUrl, errors) {

  // Configures card header.
  let cardHeader = CardService.newCardHeader()
    .setTitle('Share macros with other spreadheets!')
    .setImageUrl(ADDON_LOGO)
    .setImageStyle(CardService.ImageStyle.SQUARE);

  // If form errors exist, configures section with error messages.
  let showErrors = false;

  if (errors && errors.length) {
    showErrors = true;
    let msg = errors.reduce((str, err) => `${str}• ${err}<br>`, '');
    msg = `<b>Form submission errors:</b><br><font color="#ba0000">${msg}</font>`;

    // Builds error message section.
    sectionErrors = CardService.newCardSection()
      .addWidget(CardService.newDecoratedText()
        .setText(msg)
        .setWrapText(true));
  }

  // Configures source project section.
  let sectionSource = CardService.newCardSection()
    .addWidget(CardService.newDecoratedText()
      .setText('<b>Source macro</b><br>The Apps Script project to copy'))

    .addWidget(CardService.newTextInput()
      .setFieldName('sourceScriptId')
      .setValue(sourceScriptId || '')
      .setTitle('Script ID of the source macro')
      .setHint('You must have at least edit permission for the source spreadsheet to access its script project'))

    .addWidget(CardService.newTextButton()
      .setText('Find the script ID')
      .setOpenLink(CardService.newOpenLink()
        .setUrl('https://developers.google.com/apps-script/api/samples/execute')
        .setOpenAs(CardService.OpenAs.FULL_SIZE)
        .setOnClose(CardService.OnClose.NOTHING)));

  // Configures target spreadsheet section.
  let sectionTarget = CardService.newCardSection()
    .addWidget(CardService.newDecoratedText()
      .setText('<b>Target spreadsheet</b>'))

    .addWidget(CardService.newTextInput()
      .setFieldName('targetSpreadsheetUrl')
      .setValue(targetSpreadsheetUrl || '')
      .setHint('You must have at least edit permission for the target spreadsheet')
      .setTitle('Target spreadsheet URL'));

  // Configures help section.
  let sectionHelp = CardService.newCardSection()
    .addWidget(CardService.newDecoratedText()
      .setText('<b><font color=#c80000>NOTE: </font></b>' +
        'The Apps Script API must be turned on.')
      .setWrapText(true))

    .addWidget(CardService.newTextButton()
      .setText('Turn on Apps Script API')
      .setOpenLink(CardService.newOpenLink()
        .setUrl('https://script.google.com/home/usersettings')
        .setOpenAs(CardService.OpenAs.FULL_SIZE)
        .setOnClose(CardService.OnClose.NOTHING)));

  // Configures card footer with action to copy the macro.
  var cardFooter = CardService.newFixedFooter()
    .setPrimaryButton(CardService.newTextButton()
      .setText('Share macro')
      .setOnClickAction(CardService.newAction()
        .setFunctionName('onClickFunction_')));

  // Begins building the card.
  let builder = CardService.newCardBuilder()
    .setHeader(cardHeader);

  // Adds error section if applicable.
  if (showErrors) {
    builder.addSection(sectionErrors)
  }

  // Adds final sections & footer.
  builder
    .addSection(sectionSource)
    .addSection(sectionTarget)
    .addSection(sectionHelp)
    .setFixedFooter(cardFooter);

  return builder.build();
}

/**
 * Action handler that validates user inputs and calls shareMacro_
 * function to copy Apps Script project to target spreadsheet.
 * 
 * @param {Object} e - Add-on event object.
 * 
 * @return {CardService.Card} Responds with either a success or error card.
 */
function onClickFunction_(e) {

  const sourceScriptId = e.formInput.sourceScriptId;
  const targetSpreadsheetUrl = e.formInput.targetSpreadsheetUrl;

  // Validates inputs for errors.
  const errors = [];

  // Pushes an error message if the Script ID parameter is missing.
  if (!sourceScriptId) {
    errors.push('Missing script ID');
  } else {

    // Gets the Apps Script project if the Script ID parameter is valid.
    const sourceProject = APPS_SCRIPT_API.get(sourceScriptId);
    if (!sourceProject) {
      // Pushes an error message if the Script ID parameter isn't valid.
      errors.push('Invalid script ID');
    }
  }

  // Pushes an error message if the spreadsheet URL is missing.
  if (!targetSpreadsheetUrl) {
    errors.push('Missing Spreadsheet URL');
  } else
    try {
      // Tests for valid spreadsheet URL to get the spreadsheet ID.
      const ssId = SpreadsheetApp.openByUrl(targetSpreadsheetUrl).getId();
    } catch (err) {
      // Pushes an error message if the spreadsheet URL parameter isn't valid.
      errors.push('Invalid spreadsheet URL');
    }

  if (errors && errors.length) {
    // Redisplays form if inputs are missing or invalid.
    return createSelectionCard(e, sourceScriptId, targetSpreadsheetUrl, errors);

  } else {
    // Calls shareMacro function to copy the project.
    shareMacro_(sourceScriptId, targetSpreadsheetUrl);

    // Creates a success card to display to users.
    return buildSuccessCard(e, targetSpreadsheetUrl);
  }
}

/**
 * Builds success card to inform user & let them open the spreadsheet.
 * 
 * @param {Object} e - Add-on event object.
 * @param {string} targetSpreadsheetUrl - URL of the target spreadsheet.
 * 
 * @return {CardService.Card} Returns success card.
 */function buildSuccessCard(e, targetSpreadsheetUrl) {

  // Configures card header.
  let cardHeader = CardService.newCardHeader()
    .setTitle('Share macros with other spreadsheets!')
    .setImageUrl(ADDON_LOGO)
    .setImageStyle(CardService.ImageStyle.SQUARE);

  // Configures card body section with success message and open button.
  let sectionBody1 = CardService.newCardSection()
    .addWidget(CardService.newTextParagraph()
      .setText('Sharing process is complete!'))
    .addWidget(CardService.newTextButton()
      .setText('Open spreadsheet')
      .setOpenLink(CardService.newOpenLink()
        .setUrl(targetSpreadsheetUrl)
        .setOpenAs(CardService.OpenAs.FULL_SIZE)
        .setOnClose(CardService.OnClose.RELOAD_ADD_ON)));
  let sectionBody2 = CardService.newCardSection()
    .addWidget(CardService.newTextParagraph()
      .setText('If you don\'t see the copied project in your target spreadsheet,' +
       ' make sure you turned on the Apps Script API in the Apps Script dashboard.'))
    .addWidget(CardService.newTextButton()
      .setText("Check API")
      .setOpenLink(CardService.newOpenLink()
        .setUrl('https://script.google.com/home/usersettings')
        .setOpenAs(CardService.OpenAs.FULL_SIZE)
        .setOnClose(CardService.OnClose.RELOAD_ADD_ON)));

  // Configures the card footer with action to start new process.
  let cardFooter = CardService.newFixedFooter()
    .setPrimaryButton(CardService.newTextButton()
      .setText('Share another')
      .setOnClickAction(CardService.newAction()
        .setFunctionName('onHomepage')));

  return builder = CardService.newCardBuilder()
    .setHeader(cardHeader)
    .addSection(sectionBody1)
    .addSection(sectionBody2)
    .setFixedFooter(cardFooter)
    .build();
 }

appsscript.json

решения/надстройка/share-macro/appsscript.json

Посмотреть на GitHub

{
  "timeZone": "America/Los_Angeles",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/drive.readonly",
    "https://www.googleapis.com/auth/script.projects"
  ],
    "urlFetchWhitelist": [
    "https://script.googleapis.com/"
  ],
  "addOns": {
    "common": {
      "name": "Share Macro",
      "logoUrl": "https://www.gstatic.com/images/branding/product/2x/apps_script_48dp.png",
      "layoutProperties": {
        "primaryColor": "#188038",
        "secondaryColor": "#34a853"
      },
      "homepageTrigger": {
        "runFunction": "onHomepage"
      }
    },
    "sheets": {}
  }
}

Авторы

Этот образец поддерживается Google с помощью экспертов-разработчиков Google.

Следующие шаги

• Расширьте Google Workspace с помощью надстроек
• Создание дополнений для Google Workspace
• Опубликовать приложение