Сценарий приложений: как вы копируете значения из определенных столбцов в другую электронную таблицу с помощью триггера времени, нажатия кнопки или обновления листа?

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

Вот мои примеры таблиц:

Основная таблица (два листа: 1 – лист со вставленными значениями рядом со столбцами комментариев пользователей. 2 — лист с кнопкой, которая запускает скрипт приложений)

Таблица оперативных данных (извлекает и форматирует необходимые данные из других таблиц).

Ранее я использовал .copyTo() для копирования значений данных и вставки их в другое место в той же электронной таблице. Однако этот метод нельзя использовать для копирования данных в другую электронную таблицу. Копирование данных из нескольких диапазонов также вызывает у меня проблемы. Вот мой код:

function RefreshSheetData() { 

// 1a. Run the script when a button is pressed (Main Spreadsheet - ‘Update report button!A3’) 
// 1b. Run the script at a certain time (every Monday at 10 AM) 
// 1c. Run the script when data in a sheet is replaced with new data via a formula – NOT POSSIBLE. 
// 2. Copy email address and user comments (Main Spreadsheet - Editable report - J3:J & AM3:AR) 
// 3. Clear the “Updated user comments” sheet below the header row (Live data Spreadsheet - Updated user comments - A2:G) 
// 4. Paste the values (Live data Spreadsheet - Updated user comments - A2:G) 
// 5. Clear the “Editable report” below the header rows (Main Spreadsheet - Editable report – B3:AR) 
// 6. Copy the Live sheet (which should now include the most recent user comments via array vlookup) (Live data Spreadsheet - live data – A3:AQ) 
// 7. Paste the values (Main Spreadsheet - Editable report - B3:AR) 
// 8. Add the (United Kingdom) time and date (Main Spreadsheet - Update report button - A10) 
// 9. Add the time and date (Live data Spreadsheet - Updated user comments - J1) 

ScriptApp.newTrigger('RefreshSheetData') 
.timeBased() 
.onWeekDay(ScriptApp.WeekDay.MONDAY) 
.atHour(10) 
.create(); 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var startSheet = ss.getSheetByName('Editable report');
var sourceRange = startSheet.getRangeList(['J3:J', 'AM3:AR']);
var sourceValues = sourceRange.getValues();

var target = SpreadsheetApp.openById('1OHQHefYvE4vZZPr8jgziy_L3-UBf1WSoKzMWQ8LUz6w');
var targetSheet = target.getSheetByName('Updated user comments');

var clearTargetRange = targetSheet.getRange('A2:G').clearContent();

var targetRange = targetSheet.getRange('A2').setValues(sourceValues);  

var liveTargetSheet = target.getSheetByName('Live data');
var liveSourceRange = liveTargetSheet.getRange('A3:AQ').getValues();

var clearMainRange = startSheet.getRange('B3:AR').clearContent();
var startRange = startSheet.getRange('B3').setValues(liveSourceRange);

SpreadsheetApp.getActive().getRange('A10').setValue(new Date());  
targetSheet.getRange('J1').setValue(new Date()) 

}

Любые советы будут оценены. Спасибо.

Часть моего вопроса заключалась в том, как запустить скрипт при изменении вывода формулы. Это невозможно. Ни триггеры .onEdit, ни .onChange не работают, поскольку они реагируют только на действия пользователя. Они не будут выполняться при изменении значения IMPORTRANGE или альтернативной формулы.


google-sheets google-apps-script array-formulas importrange
person Work in Progress    schedule 21.07.2019    source источник
comment
Добро пожаловать в StackOverFlow! Воспользуйтесь этой возможностью, чтобы пройти тур и узнать, как Как спросить и минимально воспроизводимый пример.   -  person Cooper    schedule 21.07.2019
comment
@Cooper Готово, спасибо. Я сделал это перед публикацией, поэтому, пожалуйста, дайте мне знать, если что-то неясно в моем сообщении. Я оставил свои инструкции в стиле псевдокода и фрагменты кода в редакторе скриптов примера электронной таблицы, так как думал, что пост становится длинным, но вместо этого я мог бы переместить его сюда. Тем не менее, это может быть мусорный код, поскольку он является частью базовой попытки перезапуска после нескольких часов исследований и настроек, которые привели к множеству ошибок сценария или моей попытке заставить работать код, который не работает с несколькими электронными таблицами (.copyTo и т. д.) .   -  person Work in Progress    schedule 21.07.2019
comment
Я буду рад помочь вам написать его. Я предлагаю вам начать.   -  person Cooper    schedule 22.07.2019
comment
Хорошо спасибо. Я ценю это. Я переделаю сценарий приложений, а затем отредактирую свой пост, если это необходимо.   -  person Work in Progress    schedule 22.07.2019
comment
Я отредактировал свой пост и включил свой код. Моя самая большая проблема заключается в вставке столбцов, которые не находятся непосредственно рядом друг с другом, в другую электронную таблицу. Я пробовал несколько разных методов, но трудно определить, ошибаюсь ли я в синтаксисе или метод не подходит для того, что мне нужно. хотите достичь. Спасибо.   -  person Work in Progress    schedule 02.08.2019

Ответы (1)


arrow_upward
1
arrow_downward

Вот несколько предложений, относящихся к каждому из шагов, упомянутых в вашем файле скрипта приложений.

  1. 1б. Чтобы запустить скрипт в определенное время, используйте устанавливаемый триггер. «Время движет»

    1с. Чтобы запустить скрипт при обновлении данных на листе: используйте onEdit

    1д. Вы можете комбинировать все триггеры, просто добавляя столько, сколько требуется.

  2. Чтобы копировать и вставлять данные, вам просто нужны методы getValues() и setValues(), которые вы уже использовали, главное, чтобы вы правильно выбрали диапазон «для копирования».

  3. Вы можете очистить диапазон с помощью clear().

  4. См. 3.

  5. Вы можете скопировать лист с помощью copyTo(). , однако имейте в виду, что при использовании этого метода ваши данные на скопированном листе будут автоматически обновляться, если в исходном листе есть изменения. Если вы хотите, чтобы значения оставались статическими, вам нужно скопировать и вставить их с помощью функций copyValues() и setValues().

  6. См. 2. и 5.

  7. См. здесь, как получить и отформатировать дату в Apps Script.

  8. Назначьте дату переменной и используйте setValue()

Я рекомендую вам попробовать создать скрипт самостоятельно, основываясь на этих шагах, из документации по скрипту приложений. дает вам хороший справочник и руководство, как это сделать. Если вы столкнетесь с конкретными проблемами на одном из шагов, которые вы не можете решить с помощью документации, не стесняйтесь спрашивать!

person ziganotschka    schedule 22.07.2019
comment
Большое спасибо. Я прочитаю это, повторю свой сценарий приложений, а затем спрошу, нужна ли дополнительная помощь. - person Work in Progress; 22.07.2019
comment
Спасибо за ссылку на документацию. Я отредактировал свой код. Моя основная проблема заключается в замене .CopyTo, так как его нельзя использовать для копирования и вставки данных в другую электронную таблицу. Моя другая проблема — копирование столбцов, которые не находятся непосредственно рядом друг с другом. - person Work in Progress; 02.08.2019
comment
Не путайте метод copyTo() для диапазонов и листов. Последний можно использовать для копирования целых листов в другую электронную таблицу. developers.google.com/apps-script/reference/spreadsheet / Однако, если вы хотите скопировать только диапазон в другую таблицу, вам нужно использовать getValues() и setValues(). - person ziganotschka; 02.08.2019
comment
Ах я вижу. Это приятно знать, спасибо. Я пытаюсь .getValues() и .setValues(). Ошибка, с которой я сталкиваюсь: TypeError: не удается найти функцию getValues ​​​​в объекте RangeList. - person Work in Progress; 06.08.2019
comment
var sourceValues = sourceRange.getValues(); Строка 21 в этом посте. Строка 33 в примере листа Google (в коде есть еще несколько заметок, которые я удалил здесь для простоты использования). Спасибо. - person Work in Progress; 08.08.2019
comment
Проблема в том, что вы пытаетесь получить значения из двух несмежных диапазонов, содержащихся в списке диапазонов. См. это: stackoverflow.com/questions/36234752/ Лучше, если вы сделаете var sourceRange1 = startSheet.getRange('J3:J'); var sourceValues1 = sourceRange1.getValues(); var sourceRange2 = startSheet.getRangeList('AM3:AR'); var sourceValues2 = sourceRange2.getValues(); В качестве альтернативы вы можете установить RangeListApp, который позволит вам напрямую получать значения из списков диапазона: github.com/tanaikech/RangeListApp - person ziganotschka; 08.08.2019
comment
Спасибо за предложение нескольких решений. Сначала я попробовал ваш код, а затем код связанного вопроса. Однако я получаю сообщение об ошибке Количество строк в данных не соответствует количеству строк в диапазоне. Данные имеют 7009, но диапазон имеет 1 из строки var targetRange1 = targetSheet.getRange('A2').setValues(sourceValues1);. Есть ли способ обойти это или вы предлагаете вместо этого установить RangeListApp? - person Work in Progress; 11.08.2019
comment
Вам нужно знать размер вашего исходного диапазона (например, с sourceValues1.length ) и назначить матрицу sourceValues1 диапазону того же размера, а не одной ячейке «A2». Я рекомендую вам потратить некоторое время на изучение руководств, справочников и примеров по скриптам приложений, чтобы получить более глубокое представление о существующих методах и их использовании. - person ziganotschka; 11.08.2019
comment
ХОРОШО. Размер исходного диапазона изменится. Я вернусь к документации. Спасибо. - person Work in Progress; 12.08.2019