Google Apps脚本替代SUMIF [英] Google Apps Script alternative to SUMIF
问题描述
我对JavaScript还是很陌生,并且希望通过编写一些简单的脚本来帮助我实现一些流程的自动化,从而促进我的学习.我的第一个脚本旨在从电子表格中查找客户的预算,这样我就可以计算出广告系列是否需要暂停.
I'm fairly new to JavaScript and am looking to progress my learning through making a few easy scripts that will help me automate some processes. My first script is looking to lookup the client's budget from a spreadsheet so I can then calculate whether the campaigns need pausing.
到目前为止,我已经知道了:
So far I've got this:
function main() {
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/sheetname/edit';
var SHEET_NAME = 'Main';
var clientName = 'Client';
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = ss.getSheetByName(SHEET_NAME);
var cell = sheet.getRange("L5");
cell.setFormula('=SUMIF(C5:C100, "' + clientName + '", D5:D100)');
Logger.log(clientName + ' budget is £' + cell.getValue());
}
Column C = Client Name
和D = Budget
的地方.
任何人都可以在不需要在工作表中设置公式的情况下,为我提供一种更好的方式来检查客户预算的见解吗?
Would anybody be able to offer insight into perhaps a better way for me to check the clients budget without having to set the formula in the sheet?
谢谢.
推荐答案
此修改如何?
- 首先,获取"C5:D100"的值.
- 使用检索到的值计算
SUMIF()
.
function main() {
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/sheetname/edit';
var SHEET_NAME = 'Main';
var clientName = 'Client';
var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = ss.getSheetByName(SHEET_NAME);
var values = sheet.getRange("C5:D100").getValues();
var res = 0;
for (var i in values) {
if (values[i][0] == clientName) {
res += values[i][1];
}
}
sheet.getRange("L5").setValue(res); // I'm not sure whether this is required.
Logger.log(clientName + ' budget is £' + res);
}
如果我误解了你的问题,对不起.
If I misunderstand your question, I'm sorry.
这篇关于Google Apps脚本替代SUMIF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!