Блог

Я сделал этот лист, чтобы создавать счета-фактуры для отправки за выполняемую нами работу.

Основная вкладка (Счет-фактура) — это то, что мы на самом деле распечатываем и отправляем. В основном каждая ячейка на вкладке счет-фактура содержит формулу для извлечения информации с других 2 вкладок (безбумажных и клиентов) для фактического заполнения счета-фактуры. Вы выбираете клиента из выпадающего меню, а затем информация о счете заполняется с использованием информации с вкладок «Клиенты», а остальная часть счета заполняется с использованием информации с безбумажной вкладки.

Вся информация на безбумажной вкладке (даты обслуживания, отработанные часы, пройденные мили, код обслуживания и т.д.) заполняется из внешнего источника, нашего тайм-замка.

У меня уже есть формулы на вкладке счета, которые выполняют за меня 90% работы, возможно, это не самые элегантные формулы, но они выполняют свою работу. До недавнего времени они выполняли 100% работы, но произошли изменения в тарифах выставления счетов за определенный код услуги, и вот тут-то у меня и возникла проблема.

Вы можете видеть в разделе Тариф выставления счетов в ColI, что у меня есть следующая формула

=IF(B12="1:1 ADL (OR526)", "$32.23", IF(B12="2:1 ADL (OR526-ZE)","$64.46", IF(B12="1:1 at DSA Event (OR542-R1)", "$32.23", IF(B12="Group DSA (OR542-W2)", "$18.61","")))

Не очень красиво, но это просто, и раньше это работало нормально. Теперь проблема, с которой я столкнулся, заключается в том, что код обслуживания «Group DSA (OR542-W2)» имеет несколько тарифов, которые варьируются в зависимости от клиента. Тарифы следующие$18.06, $18.61, $20.15, $22.03, $24.29, и 27,11 доллара.

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

 function addValidation() {

  // Get the spreadsheet and active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // Get the data as an Array you can iterate and manipulate
  var data = sheet.getDataRange().getValues();

  // Store a rule to use for the Data Validation to be added if ColB == "Group DSA (OR542-W2)"
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['$18.06', '$18.61', '$20.15', '$22.03', '$24.29', '$27.11']).build();

  // Loop the sheet
  for(var i=0; i<data.length; i  ) {

    // Test ColB. Note that the array is 0-indexed, so A=0, B=2, etc...
    // To change which columns you're testing, change the second value.
    if(data[i][1] == "Group DSA (OR542-W2)") {

      // If it's "Group DSA (OR542-W2)," add the Data Validation rule to Col I for that row.
      // Note that .getRange() is _not_ 0 indexed, which is why you need `i 1` to get the correct row

      sheet.getRange(i 1, 9).clear().setDataValidation(rule);

      // If ColB == "1:1 ADL (OR526)," mark ColI as "32.23"
    } else if(data[i][1] == "1:1 ADL (OR526)") {
      sheet.getRange(i 1, 9).clearDataValidations().setValue("32.23");
    }
  }
} 

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

 function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="B12"){
  var val=e.range.getValue()
    if(val=="1:1 ADL (OR526)"){
      s.getRange("I12").setDataValidation(null)
      s.getRange("I12").setValue("$32.23")
  }
      else{
  s.getRange('I12').clearContent()      
  var cell = s.getRange('I12');
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['$18.06', '$18.61', '$20.15', '$22.03', '$24.29', '$27.11']).build();
  cell.setDataValidation(rule);
   }}} 

Я не программист, я знаю очень простые языки python и Java, но здесь нет ничего, что могло бы помочь. Обычно я могу смешать кучу формул вместе, чтобы что-то сработало, но в данном случае я просто в растерянности. Я уже несколько дней бьюсь головой о стену. Если есть кто-нибудь, кто готов мне помочь или даже указать мне в нужном направлении некоторые материалы для чтения, которые могли бы мне помочь, я был бы очень признателен.

Заранее спасибо.

Я бы предложил использовать формулу IFS в формуле ur

Вы можете ознакомиться с этой статьей для получения справки о формуле IFS , https://support.google.com/docs/answer/7014145

 ...IF(B12="Group DSA (OR542-W2)", IFS(condition1,"$18.06",condition2,"$18.61",condition3,"$20.15")
 

Вы должны создать таблицу, в которой перечислить, какие клиенты классифицируются по каким тарифам в рамках услуги «Group DSA (OR542-W2)», а затем с помощью VLOOKUP получить их имя и их тарифы будут легко доступны.

Таким образом, это упростит вам работу, так как для этого вам не придется использовать скрипт приложений.