我仍在努力理解循环。
我有一个脚本,可以计算两个地址之间的距离(以公里为单位)。此时,它仅计算第12行的距离,并将结果放入C列的第12行。
Start End Total km's
address one address two 16,283
代码(可在网上找到):
function GOOGLEMAPS() {
var start_address = Spreadsheetapp.getactiveSheet().getRange('A12').getvalue();
var end_address = Spreadsheetapp.getactiveSheet().getRange('B12').getvalue();
var mapObj = Maps.newDirectionFinder();
mapObj.setOrigin(start_address);
mapObj.setDestination(end_address);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = getTheLeg["distance"]["value"];
var keerduizend = meters / 1000;
Spreadsheetapp.getactiveSheet().getRange('C12').setvalue(keerduizend);
}
最终结果应该是它可以计算出工作表中的所有距离:
A B C
Start End Total km's
address one address two 16,283
address two address one 16,283
address three address four 16,283
address five address six 16,283
Total SUM(C12:C15)
有4个地址还是40个地址都没关系,用户应该可以添加行(在最后一个有地址的行和总计的行之间。因为我仍在努力理解循环,所以我看不到如何做到这一点。
有人可以向我解释如何实现这一目标吗?
修改
感谢迭戈,脚本的第一部分工作:
function GOOGLEMAPS() {
var sheet = Spreadsheetapp.getactiveSheet();
var start_addresses = sheet.getRange('B12:B26').getvalues(); // [["address one"],["address two"],["address three"],["address five"]]
var end_addresses = sheet.getRange('D12:D26').getvalues(); // [["address two"],["address one"],["address four"],["address six"]]
var distances = []; // This will store all of the distances
for (var i = 0; i < start_addresses.length; i++) {
var mapObj = Maps.newDirectionFinder();
if (start_addresses[i][0] != "" && end_addresses[i][0] != "") {
mapObj.setOrigin(start_addresses[i][0]);
mapObj.setDestination(end_addresses[i][0]);
var directions = mapObj.getDirections();
var getTheLeg = directions["routes"][0]["legs"][0];
var meters = getTheLeg["distance"]["value"];
var keerduizend = meters / 1000;
distances.push([keerduizend]);
}
}
sheet.getRange('E12:E26').setvalues(distances);
}
仍然出现错误
The number of rows in the data does not match the number of rows in the range. The data has 3,but the range has 15. (line 30,file 'Code')
但是我会首先尝试自己解决这个问题。