Google spreadsheet search for part ,replace and delete

Question

I'm trying to start a project using Tasker(an app developed for android) It has lots of useful plugins and I have stumbled upon google spreadsheet plugin. With this plugin i can easily read from a spreadsheet.
But my problem is writing into the spreadsheet. I want to use the spreadsheet as a database for my tasker app.

This means it will write simple values to the Spreadsheet examples
A2 = Nexus5/off:1:8:1
A3 = Nexus6/on:2:3:4

I am now trying to script a search and replace within google scripts. And im getting stuck.
What i want it to do is if my Nexus5 turns on it will send to the spreadsheet in A1 Nexus5/on:1:8:1.

The script has to search for Nexus5/ in column A and replace the cell value with the new value. After that it has to delete A1 so new input can be put in. Beneath is the script i got so far it can search for an input I put in A1 and replace it in the list with test. But I can't seem to get it search just for the first part.

function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
    var values = sheet.getDataRange().getValues();
  //loop over the rows in the array
  for(var row in values){
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      return original_value.toString().replace(to_replace,replace_with);
    });
    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
  //write the updated values to the sheet
  sheet.getDataRange().setValues(values);
}
function onChange(e){
  var sheet = SpreadsheetApp.getActiveSheet()
  replaceInSheet(sheet,val,'test');
}

Please explain your problem in a little more depth as i’m having trouble working out what you want to happen. Perhaps mockups of before & after? — Dean Ransevycz
– Dean Ransevycz, Commented Nov 18, 2017 at 2:59

Dean Ransevycz · Accepted Answer · 2017-11-18 05:58:51Z

0

You want to use regexes to crack this nut. There is a comprehensive set of answers here.

In short, set your search to /^Nexus5\// (either with that raw regex declaration, or creating a new RegExp() object) & just use string.replace(), then write the updated values back as required.

e.g.

var myString = “Nexus5/1.2.3”;
var newValue = myString.replace(/^nexus5\/.+/i, “my new value”);

So, here we’re looking for a string beginning with “Nexus5/“ & replacing it with “my new value”. (^ anchors the search to the beginning of the string, .+ means 1 or more characters other than a line-end, we need to escape the slash - \/ - so it’s not interpreted as the end of the regex & the i after the pattern sets the search to case-insensitive.) We can now write newValue back to the source sheet however you need using the range.setValue() or range.setValues() method of the spreadsheet.

edited Nov 18, 2017 at 5:58

answered Nov 17, 2017 at 1:17

Dean Ransevycz

9539 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jens Hoofs Over a year ago

Thanks for the links. I already have read a lot about regex etc. But i can't seem to find how to use a object within a regex. And also i just want to match the first part, but replace the entire string.

Collectives™ on Stack Overflow

Google spreadsheet search for part ,replace and delete

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related