Как искать и сопоставлять, если имя листа из значения ячейки на другом листе в скрипте приложений Google и копировать ячейки

У меня есть три листа на листе 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)}
  });
}
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Улучшение производительности загрузки с помощью Google Tag Manager и атрибута Defer
Улучшение производительности загрузки с помощью Google Tag Manager и атрибута Defer
В настоящее время производительность загрузки веб-сайта имеет решающее значение не только для удобства пользователей, но и для ранжирования в...
Безумие обратных вызовов в javascript [JS]
Безумие обратных вызовов в javascript [JS]
Здравствуйте! Юный падаван 🚀. Присоединяйся ко мне, чтобы разобраться в одной из самых запутанных концепций, когда вы начинаете изучать мир...
Система управления парковками с использованием HTML, CSS и JavaScript
Система управления парковками с использованием HTML, CSS и JavaScript
Веб-сайт по управлению парковками был создан с использованием HTML, CSS и JavaScript. Это простой сайт, ничего вычурного. Основная цель -...
JavaScript Вопросы с множественным выбором и ответы
JavaScript Вопросы с множественным выбором и ответы
Если вы ищете платформу, которая предоставляет вам бесплатный тест JavaScript MCQ (Multiple Choice Questions With Answers) для оценки ваших знаний,...
0
0
101
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Предположение

Я создал другой подход, в котором я по-прежнему использовал функцию 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);
}

Выход

Рекомендации

Не стесняйтесь спрашивать, если у вас есть вопросы относительно опубликованного ответа.

PatrickdC 10.02.2023 03:48

Хорошо сконструирован и эффективен; мой подход был практически идентичен, но мой код был >100 строк; это на 60% короче.

Tedinoz 10.02.2023 05:44

@PatrickdC, это здорово! Работает так, как я просил. Спасибо. Для модификации, как можно отредактировать сценарий, чтобы, если в листе ЦЕНЫ для адреса электронной почты не было найдено кода продукта, цена на этот продукт бралась из листа ПРОДУКТЫ?

Les 10.02.2023 11:53

Добавлен дополнительный условный оператор в операторе if if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[3])).length > 0)

PatrickdC 10.02.2023 12:43

@PatrickdC, дополнительный оператор if работает отлично. Спасибо

Les 11.02.2023 08:46

@PatrickdC, у меня есть еще одна проблема: если ячейка в x[10] пуста, я добавил if (x[9] == "EURO" && x[10] != "") в условие if, но у меня есть ошибка: «Количество строк в данных не соответствует количеству строк в диапазоне. Данные имеют 5, но диапазон имеет 6". Есть ли у вас решение, как решить эту проблему? Спасибо

Les 14.02.2023 14:54

Я должен извиниться, но я думаю, что это уже считается другим вопросом, поскольку вы добавили if (x[9] == "EURO" && x[10] != ""), где я не знаком с индексом, который вы использовали. Я рекомендую вам опубликовать его как еще один вопрос, чтобы я, а также другие члены сообщества могли изучить весь новый код и проанализировать его. Что касается The data has 5 but the range has 6, я считаю, что это относится к размеру массива выходных данных (который равен 5), не соответствующему диапазону заполнителя вывода (который равен 6). Настройка getRange() строки setValues на 5 может помочь.

PatrickdC 14.02.2023 23:16

Другие вопросы по теме

Преобразовать URL-адрес с расширением .svg в изображение в ячейке - скрипт приложений Google
Можно ли с помощью API редактировать или открывать Google Sheet?
Как создать ярлыки из списка файлов в Google Apps Script?
Запуск скрипта приложений, хранящегося на Google Диске, из Google Таблиц
Как автоматически установить значение в раскрывающемся списке на основе значения из другой ячейки в строке - скрипт приложения
Как прикрепить файл документа Google как документ Microsoft Word
Как автоматически обновить пользовательский интерфейс после того, как пользователь обновит userProperty в надстройках Google Apps Script Workspace для Google Sheets?
Сценарий приложения Google: вход в систему с застежкой перестал работать
Преобразование формулы «сортировки» Google Таблиц со встроенной функцией ВПР в эквивалентный скрипт приложения
Как переместить подпапку на основе значений в столбце Google Sheet?