QUOTE(k town shit @ May 22 2020, 12:41 PM)
Hello, any idea about how to create a Macro for Google sheet? I want to automate the find and replace multiple column headers,
e.g. change column header "Name", "Nick name" to "Nama", "Nama susu"
I tried this script where I found in the forum, but it's limited to 1 column header only. I'm new to programming, can anyone teach me?
» Click to show Spoiler - click again to hide... «
function fandr() {
var r=SpreadsheetApp.getActiveSheet().getDataRange();
var rws=r.getNumRows();
var cls=r.getNumColumns();
var i,j,a,find,repl;
find="abc";
repl="xyz";
for (i=1;i<=rws;i++) {
for (j=1;j<=cls;j++) {
a=r.getCell(i, j).getValue();
if (r.getCell(i,j).getFormula()) {continue;}
try {
a=a.replace(find,repl);
r.getCell(i, j).setValue(a);
}
catch (err) {continue;}
}
}
}
your sample script is not limited to 1 column. it will actually try to replace for all available columns
problem is that it only replaces a single value
you can probably add a 3rd loop for the values to replace
but depending on the replace function, if it does partial replace then you might encounter following issue;"name" and "nick name" changed to "nama" and "nick nama" when it is replacing "name" to "nama"
if you are replacing the whole header, maybe do a compare first then set to new value to bypass above issue?