У меня есть три листа на листе JOBS, если адрес электронной почты пользователя совпадает с именем листа в рабочей книге QUOTATIONS, тогда цена берется из этого листа для указанного продукта в столбце P. Если имя листа не найдено, то цена для этого продукта берется из листа ПРОДУКТЫ. Цена также зависит от валюты, которая указана в листе ВАКАНСИИ. Пример ожидаемых результатов можно посмотреть в листе ВАКАНСИИ в столбце Q по адресу https://docs.google.com/spreadsheets/d/1up7cUvQqL-LcA7EeuM65B0zjcRT46LGwN2x3C_4j0bY/edit?usp=sharing Если у пользователя есть предложение, а электронное письмо соответствует имени листа, тогда цена поступает из рабочей книги QUOTATIONS по адресу https://docs.google.com/spreadsheets/d/10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk/edit?usp=sharing Если у пользователя нет предложения и нет соответствующего адреса электронной почты, цена берется из листа продуктов. https://docs.google.com/spreadsheets/d/1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU/edit?usp=sharing
У меня есть следующий сценарий, который соответствует цене между листом продуктов и листом вакансий, но не может также проверить, существует ли имя листа, и оно соответствует адресу электронной почты в листе вакансий, а затем получает цену из этого листа. Помощь приветствуется, спасибо
function updateQuotationPrice() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var jobSheet = ss.getSheetByName('JOBS');
var productSheet = SpreadsheetApp.openById("1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU").getSheetByName('PRODUCTS');
var quotationSheet = SpreadsheetApp.openById("10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk");
var sheetNames = []
var sheets = quotationSheet.getSheets()//.forEach(function(sheet){sheets[sheet.getName()]=sheet;});
sheets.forEach(function (sheet) {
sheetNames.push(sheet.getName());
console.info(sheets)
});
var objEuro = productSheet.getRange("A2:D" + productSheet.getLastRow()).getValues().reduce((o, r) => (o[r[0]] = r[3], o), {});
var objSterling = productSheet.getRange("A2:D" + productSheet.getLastRow()).getValues().reduce((o, r) => (o[r[0]] = r[2], o), {});
var objQuotationEuro = sheets.getRange("A2:D" + sheets.getLastRow()).getValues().reduce((o, r) => (o[r[0]] = r[3], o), {});
var objQuotationSterling = sheets.getRange("A2:D" + sheets.getLastRow()).getValues().reduce((o, r) => (o[r[0]] = r[2], o), {});
var range = jobSheet.getRange("O2:AK" + jobSheet.getLastRow());
var euroValues = range.getValues().map(r => [objEuro[r[0]] || null]);
var sterlingValues = range.getValues().map(r => [objSterling[r[0]] || null]);
var quotationEuroValues = range.getValues().map(r => [objQuotationEuro[r[0]] || null]);
var quotationSterlingValues = range.getValues().map(r => [objQuotationSterling[r[0]] || null]);
var vs = jobSheet.getRange("O2:R"+ jobSheet.getLastRow()).getValues();
vs.forEach((r) => {
if (r[0] == "EURO" && r[1].match(/^A/i) && sheets != r[3] ){
range.offset(0, 2, euroValues.length, 1).setValues(euroValues)};
if (r[0] == "STERLING" && r[1].match(/^A/i) && sheets != r[3]){
range.offset(0, 2, sterlingValues.length, 1).setValues(sterlingValues)}
if (r[0] == "EURO" && r[1].match(/^A/i) && sheets == r[3] ){
range.offset(0, 2, quotationValues.length, 1).setValues(quotationEuroValues)}
if (r[0] == "STERLING" && r[1].match(/^A/i) && sheets == r[3] ){
range.offset(0, 2, quotationValues.length, 1).setValues(quotationSterlingValues)}
});
}
Я создал другой подход, в котором я по-прежнему использовал функцию forEach()
для обработки данных. Однако я использовал функцию filter()
и includes()
, чтобы помочь в процессе сопоставления данных.
Вы можете использовать следующий сценарий в качестве основы для своего сценария.
function updateQuotationPrice() {
var ss1 = SpreadsheetApp.openById("User Update Sheet ID"); //User Update
var ss2 = SpreadsheetApp.openById("Quotation Sheet ID"); //Quotation
var ss3 = SpreadsheetApp.openById("Projects Sheet ID"); //Projects
//Extract data from User Update
var lr1 = ss1.getSheetByName("Jobs").getLastRow();
var data1 = ss1.getSheetByName("Jobs").getRange(2, 13, lr1 - 1, 4).getValues();
//Extract data from Quotation Spreadsheet
var sheetNames2 = [];
ss2.getSheets().forEach(x => sheetNames2.push(x.getName()));
//Process data 1
var output = [];
data1.forEach(x => {
if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3])).length > 0) { // added new condition here
if (x[2] == "EURO") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3]))[0][3]]);
}
else if (x[2] == "STERLING") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3]))[0][2]]);
}
}
else {
if (x[2] == "EURO") {
output.push([ss3.getSheetByName("Products").getDataRange().getValues().filter(y => y.includes(x[3]))[0][3]]);
}
else if (x[2] == "STERLING") {
output.push([ss3.getSheetByName("Products").getDataRange().getValues().filter(y => y.includes(x[3]))[0][2]]);
}
}
});
//Output
ss1.getSheetByName("Jobs").getRange(2, 17, lr1 - 1, 1).setValues(output);
}
Хорошо сконструирован и эффективен; мой подход был практически идентичен, но мой код был >100 строк; это на 60% короче.
@PatrickdC, это здорово! Работает так, как я просил. Спасибо. Для модификации, как можно отредактировать сценарий, чтобы, если в листе ЦЕНЫ для адреса электронной почты не было найдено кода продукта, цена на этот продукт бралась из листа ПРОДУКТЫ?
Добавлен дополнительный условный оператор в операторе if if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3])).length > 0)
@PatrickdC, дополнительный оператор if работает отлично. Спасибо
@PatrickdC, у меня есть еще одна проблема: если ячейка в x[10] пуста, я добавил if (x[9] == "EURO" && x[10] != "")
в условие if, но у меня есть ошибка: «Количество строк в данных не соответствует количеству строк в диапазоне. Данные имеют 5, но диапазон имеет 6". Есть ли у вас решение, как решить эту проблему? Спасибо
Я должен извиниться, но я думаю, что это уже считается другим вопросом, поскольку вы добавили if (x[9] == "EURO" && x[10] != "")
, где я не знаком с индексом, который вы использовали. Я рекомендую вам опубликовать его как еще один вопрос, чтобы я, а также другие члены сообщества могли изучить весь новый код и проанализировать его. Что касается The data has 5 but the range has 6
, я считаю, что это относится к размеру массива выходных данных (который равен 5), не соответствующему диапазону заполнителя вывода (который равен 6). Настройка getRange()
строки setValues
на 5 может помочь.
Не стесняйтесь спрашивать, если у вас есть вопросы относительно опубликованного ответа.