#google-apps-script #google-sheets
Вопрос:
Я сделал этот лист, чтобы создавать счета-фактуры для отправки за выполняемую нами работу.
Основная вкладка (Счет-фактура) — это то, что мы на самом деле распечатываем и отправляем. В основном каждая ячейка на вкладке счет-фактура содержит формулу для извлечения информации с других 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, но здесь нет ничего, что могло бы помочь. Обычно я могу смешать кучу формул вместе, чтобы что-то сработало, но в данном случае я просто в растерянности. Я уже несколько дней бьюсь головой о стену. Если есть кто-нибудь, кто готов мне помочь или даже указать мне в нужном направлении некоторые материалы для чтения, которые могли бы мне помочь, я был бы очень признателен.
Заранее спасибо.
Ответ №1:
Я бы предложил использовать формулу 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 получить их имя и их тарифы будут легко доступны.
Таким образом, это упростит вам работу, так как для этого вам не придется использовать скрипт приложений.
Комментарии:
1. О, ничего себе, это делает свое дело. Большое вам спасибо! Мне придется еще немного почитать о том, что я могу сделать, чтобы все упростить, потому что сейчас у меня 68 клиентов с различными тарифами выставления счетов, поэтому, если я буду использовать формулу как есть, она будет немного раздутой, но она будет работать! и это поможет мне начать. Я очень новичок во всем этом, но я учусь. так что я не сомневаюсь, что смогу улучшить ситуацию здесь теперь, когда у меня есть отправная точка. Я ценю тебя!
2. если у вас так много клиентов, я предлагаю вам вместо этого использовать функцию фильтра. Вы можете посмотреть его на support.google.com/docs/answer/3093197?hl=en . Короче говоря , вам все равно следует создать таблицу с различными ставками, чтобы в будущем вы могли получать доступ к данным и соответствующим образом редактировать их. Юаней, чтобы отметить мой ответ, если это помогло вам 😀
3. Я нажал кнопку «Голосовать» (возможно, здесь это называется не так, но это все, что я знаю благодаря reddit), но она мне не позволила, потому что моя репутация слишком низкая. Там было написано: «Спасибо за отзыв! Вам нужно не менее 15 репутации, чтобы проголосовать, но ваши отзывы были записаны». Но я все равно ценю вашу помощь! 🙂