连接几个单元格并从串联创建链接(Concatenate a few cells and creating a link from concatenation)

连接几个单元格并从串联创建链接(Concatenate a few cells and creating a link from concatenation)

我正在尝试创建一个脚本，其中假设列I包含地址列J包含城市列K包含邮政编码假设有数千个条目

我想要一个脚本来连接这些值并在列I本身上创建一个谷歌地图链接。

我确实设法使链接部分部分工作，但在创建链接之前无法将其连接起来。

function googlemaps() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var r = ss.getActiveRange() + ss.getActiveRange().offset(0, 0, 1, 2); // var t = r.offset(1, 0, 2); var v = r.getValues(); for(var i=0;i<v.length;i++) { for(var j=0;j<v[0].length;j++) { v[i][j] = '=HYPERLINK("http://google.com/maps/places/'+v[i][j]+'","'+v[i][j]+'")' } } r.setValues(v); };

它一直给我这个错误

// typeError: Cannot find function getValues in object RangeRange

我知道这是抵消的事情，但我无法让它发挥作用。 我想要的一个例子

I3 = 1515 SW 2 CT J3 = Miami K3 = 33196

随着这个脚本的运行，它将改变我

=hyperlink("http://google.com/maps/places/1515 SW 2 CT Miami 33196", "1515 SW 2 CT")

应该能够在一个范围内工作。

有人请帮忙。

我也尝试过这个：

function googlemaps() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var r = ss.getActiveRange(); var r2 = ss.getActiveRange().offset(0, 1); var r3 = ss.getActiveRange().offset(0, 2); var v = r.getValues(); var v1= r2.getValues(); var v2= r3.getValues(); var vf= v + v1 + v2; for(var i=0;i<vf.length;i++) { for(var j=0;j<vf[0].length;j++) { vf[i][j] = '=HYPERLINK("http://google.com/maps/places/'+vf[i][j]+'","'+vf[i][j]+'")' } } r.setValues(vf); };

那不行也好，给我错误找不到方法setValues（string）

I am trying to create a script where by assume Column I contains Address Column J contains City Column K contains Zip code Assume the there are thousands of entries

I wanted a script that would concatenate these values and create a google maps link on column I itself.

I did manage to get the link part working partly but could not get it to concatenate it before creating the link.

function googlemaps() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var r = ss.getActiveRange() + ss.getActiveRange().offset(0, 0, 1, 2); // var t = r.offset(1, 0, 2); var v = r.getValues(); for(var i=0;i<v.length;i++) { for(var j=0;j<v[0].length;j++) { v[i][j] = '=HYPERLINK("http://google.com/maps/places/'+v[i][j]+'","'+v[i][j]+'")' } } r.setValues(v); };

It keeps giving me this error

// typeError: Cannot find function getValues in object RangeRange

I know it's the offset thing but I can't get it to work. An example of what i want would be

I3 = 1515 SW 2 CT J3 = Miami K3 = 33196

And with this script run, it would change I in

=hyperlink("http://google.com/maps/places/1515 SW 2 CT Miami 33196", "1515 SW 2 CT")

should be able to work across a range.

Anyone please help.

I tried this as well:

function googlemaps() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var r = ss.getActiveRange(); var r2 = ss.getActiveRange().offset(0, 1); var r3 = ss.getActiveRange().offset(0, 2); var v = r.getValues(); var v1= r2.getValues(); var v2= r3.getValues(); var vf= v + v1 + v2; for(var i=0;i<vf.length;i++) { for(var j=0;j<vf[0].length;j++) { vf[i][j] = '=HYPERLINK("http://google.com/maps/places/'+vf[i][j]+'","'+vf[i][j]+'")' } } r.setValues(vf); };

and that does not work as well, gives me the error Cannot find method setValues(string)

最满意答案

您也可以只使用公式来完成此操作。

I3 = 1515 SW 2 CT J3 = Miami K3 = 33196 L3 = =HYPERLINK(CONCATENATE("http://maps.google.com/maps? f=q&source=s_q&hl=en&geocode=&q=",I3," ",J3," ",K3),"Google Map")

对不起，我不明白你的要求。 尝试这个。

function googlemaps() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = ss.getLastRow() var r = ss.getSheetValues(3,9,lr-2,3);//Assunes address data starts on row 3 for(var i=0;i<lr-2;i++) { var addr = r[i][0]+' ' +r[i][1]+' ' +r[i][2] // Concatenates address data r[i][9] = '=HYPERLINK("http://maps.google.com/maps? f=q&source=s_q&hl=en&geocode=&q='+addr+'","'+addr+'")' ss.getRange(i+3,9).setFormula(r[i][9]); ss.getRange(i+3,10).clear(); //Clears City ss.getRange(i+3,11).clear(); //Clears Zip } };

这是另一个版本，而不是创建一个超链接数组，并一次写回一次而不是一行。 它要快得多。 我用4000个地址测试了它。 此外，我将其更改为从第2行开始（假设标题行）。 如果您需要调整它的起始位置，请使用Google setRange（1,1,1,1）表示法来解释每个数字的含义。 它控制起始行。 如果更改起始行，则需要更改lr-1，以使总行数正确。

function googlemaps1() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = ss.getLastRow() var r = ss.getSheetValues(2,9,lr-1,3);//Assunes address data starts on row 2 var arr = [] for(var i=0;i<lr-1;i++) { var addr = r[i][0]+' ' +r[i][1]+' ' +r[i][2] // Concatenates address data r[i][9] = '=HYPERLINK("http://maps.google.com/maps? f=q&source=s_q&hl=en&geocode=&q='+addr+'","'+addr+'")' arr.push([r[i][9]]) } var clr = ss.getRange(2,10,arr.length,2).clear();//clear state and zip var destrange = ss.getRange(2,9,arr.length,1); destrange.setFormulas(arr);// set all hyperlinks };

You can also do this with just a formula.

I3 = 1515 SW 2 CT J3 = Miami K3 = 33196 L3 = =HYPERLINK(CONCATENATE("http://maps.google.com/maps? f=q&source=s_q&hl=en&geocode=&q=",I3," ",J3," ",K3),"Google Map")

Sorry I did not understand your requirement. Try this.

function googlemaps() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = ss.getLastRow() var r = ss.getSheetValues(3,9,lr-2,3);//Assunes address data starts on row 3 for(var i=0;i<lr-2;i++) { var addr = r[i][0]+' ' +r[i][1]+' ' +r[i][2] // Concatenates address data r[i][9] = '=HYPERLINK("http://maps.google.com/maps? f=q&source=s_q&hl=en&geocode=&q='+addr+'","'+addr+'")' ss.getRange(i+3,9).setFormula(r[i][9]); ss.getRange(i+3,10).clear(); //Clears City ss.getRange(i+3,11).clear(); //Clears Zip } };

Here is another version than creates an array of the hyperlinks and writes them back once instead of a row at a time. It is much faster. I tested it with 4000 addresses. Also, I changed it to start on row 2 (assuming a header row). If you need to adjust where it starts, Google setRange(1,1,1,1) notation for an explaination of what each number means. It controls the start row. Also the lr-1 would need to be changed if you change the start row so the total number of rows will be correct.

function googlemaps1() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var lr = ss.getLastRow() var r = ss.getSheetValues(2,9,lr-1,3);//Assunes address data starts on row 2 var arr = [] for(var i=0;i<lr-1;i++) { var addr = r[i][0]+' ' +r[i][1]+' ' +r[i][2] // Concatenates address data r[i][9] = '=HYPERLINK("http://maps.google.com/maps? f=q&source=s_q&hl=en&geocode=&q='+addr+'","'+addr+'")' arr.push([r[i][9]]) } var clr = ss.getRange(2,10,arr.length,2).clear();//clear state and zip var destrange = ss.getRange(2,9,arr.length,1); destrange.setFormulas(arr);// set all hyperlinks };