Question

我有两篇文字,从谷歌表中删除案文。这两本书在更新时都自动启动,但两本书都试图将同一个牢房隔开,而只是应用其中一项功能。

Oringinal cell text example: From: Leigh-on-sea, ss9, UK Building No. Street & Postcode: 123 street address UK Distance: 0.7m Duration: 4 min

删除案文的职能:“标题:

function removeTags() {
  var column = 11
  var sh = SpreadsheetApp.getActive().getActiveSheet();
  var data = sh.getRange(1, column, sh.getLastRow(), 1).getValues();
  for (var n in data) {
    if (data[n][0] ==   ) { continue };// if cell empty then continue
    var str = data[n][0].toString();
    var location = str.indexOf("Postcode: ");
    if (location != -1) {
      data[n][0] = str.substring(location + 10, str.length - 1);//remove everything BEFORE Postcode: 
    }
  }
  sh.getRange(1, column, sh.getLastRow(), 1).setValues(data);// update sheet with new data
}

Function to remove text AFTER "Distance: "

function removeTags(){
  var column = 11
  var sh = SpreadsheetApp.getActive().getActiveSheet();
  var data = sh.getRange(1,column,sh.getLastRow(),1).getValues();
  for(var n in data){
    if(data[n][0]==  ){continue};// if cell empty then continue
    var str = data[n][0].toString();
    data[n][0]= str.substring(0,str.indexOf("Distance: "));//remove everything after Distance: 
  }
  sh.getRange(1,column,sh.getLastRow(),1).setValues(data);// update sheet with new data
}

这两项职能在单独运作时都是预期的,但当新财产被送至表格时,则只适用第一份触发/说明。我需要把文字合在一起,以便有一个单一的触发点。

I tried editing a bit of the script a few way to figure it out myself, but failed.

Answer 1

Suggestion: Use `slice` instead of `substring`

iii 一条航道建议是有效的;你必须把两项职能结合起来,因为只有一份电线触发器才能适用于正在修改的电池。我如何这样做,就是在发言之前和之后lic弄和删除案文,我稍微修改了你的文字。

努力:

function removeTags() {
  var column = 11
  var sh = SpreadsheetApp.getActive().getActiveSheet();
  var data = sh.getRange(1, column, sh.getLastRow(), 1).getValues();
  for (var n in data) {
    if (data[n][0] ==   ) { continue };// if cell empty then continue
    var str = data[n][0].toString();
    var startLocation = str.indexOf("Postcode: ")+10; //returns the index of "Postcode: " + 10
    var endLocation = str.indexOf("Distance: "); //Returns the index of "Distance: "
    if (startLocation != -1) {
      //The method below slices the string based on index stored in both startLocation and endLocation
      data[n][0] = str.slice(startLocation, endLocation)
    }
    
  }
  
  sh.getRange(1, column, sh.getLastRow(), 1).setValues(data);// update sheet with new data
}

但请注意,您的文字改动了栏内的所有行文。每当新价值被设定为触发因素时,就会增加。

If you would like the script to only modify and check just the last row, change both:

var data = sh.getRange(1, column, sh.getLastRow(), 1).getValues(); //and sh.getRange(1, column, sh.getLastRow(), 1).setValues(data);

:

var data = sh.getRange(sh.getLastRow(), column).getValues(); //and sh.getRange(sh.getLastRow(), column).setValues(data);

Reference:

slice()

Suggestion: Use slice instead of substring

Reference:

友情链接

Suggestion: Use `slice` instead of `substring`