Если вы работаете с Excel или с табличными данными, скорее всего, вам придется довольно много манипулировать данными. Я использую Google Sheets вместо Excel по многим причинам, которые я не буду раскрывать, но одним из преимуществ является совместимый с JavaScript скрипт приложений, который позволяет вам разрабатывать собственные функции, аналогичные тому, что вы делаете в VB Script (Visual Basic для Excel). Сила Apps Script заключается в использовании потрясающей силы JavaScript!
Одна вещь, которую мне приходится делать МНОГО, это переводить списки. Например, предположим, что у меня есть столбец «Категория» от поставщика, в котором категории представляют собой текстовые строки, но моя корзина электронной коммерции должна быть сопоставлена с идентификаторами категорий.
Очень часто я использую встроенную в Excel функцию SWITCH, которая позволяет вам искать что-то вроде строки и возвращать другое связанное значение. Это тот случай, когда система электронной коммерции требует ID категории вместо текстовой строки.
Оператор SWITCH имеет вид:
=SWITCH(A1, "Text Category", "10", "Text Category 2", "11", "Text Category 3", "12" )
Если текст слева найден, он заменяется значением справа.
Это очень полезно во многих других случаях использования, и комбинация конкатенаций не является чем-то необычным для создания новых столбцов данных.
Использование SWITCH имеет свои ограничения. Одним из них является тот факт, что мне нужно знать, что утверждение одинаково в каждой ячейке. Если я хочу централизовать свои списки переводов, мне нужно другое решение.
Мы можем обойти это, написав собственную функцию переключения в Apps Script! В качестве простого примера мы фактически создадим нашу собственную функцию SWITCH, очень похожую на то, как приложение уже выполняет функцию, но с лучшей видимостью и более простым редактированием списка переводов.
JavaScript предоставляет нам гораздо больше возможностей для работы со списками, массивами и объектами. Используя JavaScript, вы можете создавать универсальные функции для решения самых трудоемких задач в вашем рабочем процессе.
Создать пользовательскую функцию легко!
Сначала нам просто нужно открыть новую таблицу Google, а затем перейти в Инструменты › Редактор скриптов. Откроется новое окно с пустой функцией и безымянным файлом. Идите вперед и назовите файл и сохраните. Затем удалите все содержимое и вставьте приведенный ниже код.
/**
* A custom function that converts category strings into a mapped category_id
*
* @param {String} category string
* @return {Number} id number of the category
*/
function createIds (categoryId) {
switch(categoryId){
case 'Wall Sconce': categoryId = 3; break;
case 'Pendant': categoryId = 5; break;
case 'Chandelier': categoryId = 1; break;
default: categoryId = 0;
};
return categoryId;
}
Нажмите «Сохранить», а затем нажмите «Воспроизвести» или кнопку «Выполнить», чтобы запустить скрипт. Теперь вернитесь в свой файл GSheet, перейдите в ячейку и введите = CREATEIDS («Настенный бра»)
Теперь у вас есть пользовательская функция, которая принимает входные данные ячейки и сравнивает их с наблюдениями, а затем возвращает соответствующий идентификатор, если он найден, если нет, то возвращается значение по умолчанию.
Здорово! Теперь у меня есть пользовательская функция, которую я могу использовать при создании листа импорта для моей платформы электронной коммерции, и мне нужно только взглянуть на эту функцию, чтобы проверить или обновить назначения.
Обратите внимание, что в этих блоках кода я делаю вещи ОЧЕНЬ ОЧЕНЬ простыми в надежде, что вы возьмете на себя инициативу по созданию более сложных вариантов использования. Существуют дополнительные проверки и триггеры, которые можно добавить для создания более надежных и полезных функций.
Теперь давайте сделаем еще один шаг и вместо создания нашего списка поиска в операторе SWITCH создадим еще одну вкладку, на которую мы можем ссылаться. Если вы знакомы с Excel, есть и другие функции, которые делают подобные вещи, однако это основа для создания более сложных функций, поэтому сначала освойте это!
Код такой же простой, как:
/*
* A custom function that converts category strings into a mapped category_id through a settings tab
*
* @param {String} category string from the vendor
* @return {Number} id number of the category
*
* This a very basic example of using Apps Script to compare the value of a cell to a key in a list and return an associated value
* There are no inputs, menus, or validation for simplity sake
* This shows you how to quickly mock up functionality while using other validation scripts and UI interactions can be applied after using the same logic
*
*/
//Create a a new custom function that takes the value of the cell as the paramter
function createIdsFromSettings (cat) {
//Get the sheet instance of the config sheet as active so we can retrive values
var ss = SpreadsheetApp.getActiveSpreadsheet();
//Get the sheet incstance with the configurations
var configSheet = ss.getSheetByName("Config"); //#### You can set this to the name of your sheet that has the comparison adta configuration
//Get an object with the properties of the cells A1:A100 to B1:B100 in Config sheet
var configRangeAB = configSheet.getRange('A1:B100'); //##### You can change this to any range of 2 adjacent columns
//Get an object with an array of the values of the config range
//This creates an object that contains arrays of values of the range
var configRangeABValues = configRangeAB.getValues();
//Create an empty variable to use in the below for-in object loop
var prop;
//We are just checking to make sure the input is a String we can use regex to preform better validation or many other validation methods.
if(typeof(cat) == 'string'){
for(prop in configRangeABValues){
//Here we check each value object for a value in the property array that matches the input (cat in our case)
if(cat == configRangeABValues[prop][0]){
//If we get a match we are pulling the next value out of that object's property array which is at index 1
return configRangeABValues[prop][1];
}
}
} else {
//If the input is not a string (a number for instance) we just put the text below.
//Alternatively we could color the cell or have some error message
return 'You need to enter a String!!';
};
SpreadsheetApp.flush();
}
Теперь у нас есть функция, которая сравнивает значение ячейки со значением столбца A на листах конфигурации и возвращает значение столбца B.
Выше приведен очень простой пример, который мы рассмотрим.
- Во-первых, в Apps Script API у нас есть экземпляр активного листа, который понадобится коду. Сначала мы сделаем это, создав экземпляр SpreadsheetApp.
2. Когда у нас есть экземпляр, другие методы становятся доступными для объекта листа. Затем мы вызываем метод .getRange(‘Config’), передавая имя нашего списка поиска, в данном случае я назвал его Config.
3. Как только мы получим диапазон нашего списка (столбцы A и B), нам нужно получить значения, вызвав getValues() для результирующего объекта диапазона выше.
4. Теперь, когда у нас есть значения, нам просто нужно перебрать значения столбца A, пока мы не получим совпадение для ввода ячейки. Когда мы получим совпадение, мы вернем другое значение из массива Values, используя скобки (например, configRangeABValues[prop][1]).
5. В конце приложения мы просто очищаем, чтобы убедиться, что обновления применяются вовремя.
Использование пользовательских функций в Google Таблицах невероятно эффективно, если вы хотите анализировать или обрабатывать табличные данные малого и среднего размера. Вы можете расширить функциональность GSheets, чтобы преформировать ее так же, как пользовательское приложение с помощью Apps Script, но все же есть некоторые ограничения.
Одним из ограничений является то, что в настоящее время GSheets не может обрабатывать более 2 миллионов ячеек. Это может показаться большим, но при работе с SKU электронной коммерции это может быть всего лишь один файл данных производителя!
Я регулярно работаю с большими наборами данных, которые не подходят для настольных Excel или Google Sheets. В этих случаях я полагаюсь на базы данных SQL и MongoDB для выполнения многих функций, доступных в Google Таблицах, но с большей эффективностью и большей функциональностью.
В некоторых других моих недавних статьях я обсуждаю использование MongoDB вместо Excel. Кроме того, Apps Script предлагает мощную функциональность для улучшения Google Таблиц, аналогичную той, которую вы можете достичь с помощью VB Script в Excel, но на языке, на котором написана сеть!
Хотя это иллюстрирует простой вариант использования, расширение функциональности с помощью триггеров и правил проверки создаст надежную и полезную функцию, которую вы можете использовать при подготовке документа для импорта электронной коммерции. Ознакомьтесь с документацией Apps Script для получения дополнительной информации о доступных классах и методах для объектов Sheets.
Если у вас есть вопросы об использовании скрипта приложений или импорте каталога электронной коммерции, не стесняйтесь обращаться ко мне! #Google #AppsScript #GoogleApps #Кодирование #Программирование #GoogleТаблицы
