I'm currently working on a project where I send data to a google sheet through app script.
I need to change the value of some data and used createTextFinder.
Thanks to @Mimi in this thread. But the fact is that I need to replace more than one text.
I need to change the text when the data is appending.
thanks in advance
Here is my code
function addUser(sheet) {
var FILE = SpreadsheetApp.openById("XXXXXXXXXXXXX");
var sheet = FILE.getSheetByName("Sheet1");
var id = "True";
var name = "FALSE";
var t1 = createTextFinder("TRUE").replaceAllWith("YES")
var t2 = createTextFinder("FALSE").replaceAllWith("NO")
sheet.appendRow([id,name]).t1.t2;
From I need to change the text when the data is appending., in this case, how about the following modification?
var t1 = createTextFinder("TRUE").replaceAllWith("YES")
var t2 = createTextFinder("FALSE").replaceAllWith("NO")
sheet.appendRow([id,name]).t1.t2;
var values = [id, name].map(e => {
var temp = e.toUpperCase();
return temp == "TRUE" ? "YES" : temp == "FALSE" ? "NO" : e;
});
sheet.appendRow(values);
For example, when you are required to use TextFinder, how about the following modification?
From
var t1 = createTextFinder("TRUE").replaceAllWith("YES")
var t2 = createTextFinder("FALSE").replaceAllWith("NO")
sheet.appendRow([id,name]).t1.t2;
To
sheet.appendRow([id, name]);
sheet.createTextFinder("TRUE").replaceAllWith("YES");
sheet.createTextFinder("FALSE").replaceAllWith("NO");
or, when you want to use TextFinder to the appended row, you can also the following modified script.
sheet.appendRow([id, name]);
// SpreadsheetApp.flush(); // This might be not required to be used.
const range = sheet.getRange(sheet.getLastRow(), 1, 1, 2);
range.createTextFinder("TRUE").replaceAllWith("YES");
range.createTextFinder("FALSE").replaceAllWith("NO");
function addUser() {
const txtA = ['txt1', 'txt2', 'text3'];
const rplA = ['rpl1', 'rpl2', 'repl3'];
var ss = SpreadsheetApp.openById("XXXXXXXXXXXXX");
var sh = ss.getSheetByName("Sheet1");
txtA.forEach((t, i) => {
let f = sh.createTextFinder(t).findAll();
if (f) {
f.forEach(r => { r.setValue(r.getValue().replace(txtA[i], rplA[i])); });
}
});
}