Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 how to find and replace multiple column headers, with Google script

views
     
TSk town shit
post May 22 2020, 12:41 PM, updated 4y ago

Enthusiast
*****
Junior Member
757 posts

Joined: Jan 2009
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... «

waghyu
post May 22 2020, 12:54 PM

Look at all my stars!!
*******
Senior Member
3,389 posts

Joined: Sep 2019
i use excel only and this is very easy to do.
TSk town shit
post May 22 2020, 01:04 PM

Enthusiast
*****
Junior Member
757 posts

Joined: Jan 2009
QUOTE(waghyu @ May 22 2020, 11:54 AM)
i use excel only and this is very easy to do.
*
Not talking about Excel, Excel technology is decentralized. Users always have file corruption , inconsistent version, no visibility to other team members etc.
waghyu
post May 22 2020, 04:03 PM

Look at all my stars!!
*******
Senior Member
3,389 posts

Joined: Sep 2019
QUOTE(k town shit @ May 22 2020, 01:04 PM)
Not talking about Excel, Excel technology is decentralized. Users always have file corruption , inconsistent version, no visibility to other team members etc.
*
True. But still works very well and efficiently.
TSk town shit
post May 24 2020, 10:47 PM

Enthusiast
*****
Junior Member
757 posts

Joined: Jan 2009
QUOTE(waghyu @ May 22 2020, 03:03 PM)
True. But still works very well and efficiently.
*
Not efficient, a lot of time lost in recreating the lost files.
Departments have to wait for the person to send out the individual file out in order to be updated.
That's why many businesses nowadays stick to G Suite.
waghyu
post May 25 2020, 09:15 AM

Look at all my stars!!
*******
Senior Member
3,389 posts

Joined: Sep 2019
QUOTE(k town shit @ May 24 2020, 10:47 PM)
Not efficient, a lot of time lost in recreating the lost files.
Departments have to wait for the person to send out the individual file out in order to be updated.
That's why many businesses nowadays stick to G Suite.
*
Not all biz think that way. High end technical sheet does not need to be shared and create much more value versus so collaed collaborative excel sheet.
bumpo
post May 27 2020, 10:50 AM

On my way
****
Junior Member
632 posts

Joined: Mar 2013


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... «

*
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? hmm.gif

bumpo
post May 27 2020, 10:51 AM

On my way
****
Junior Member
632 posts

Joined: Mar 2013


QUOTE(k town shit @ May 24 2020, 10:47 PM)
Not efficient, a lot of time lost in recreating the lost files.
Departments have to wait for the person to send out the individual file out in order to be updated.
That's why many businesses nowadays stick to G Suite.
*
and all it takes is one internet outage and there goes all your productivity. its not the perfect solution for everyone laugh.gif
Tullamarine
post May 28 2020, 01:09 AM

Getting Started
**
Validating
163 posts

Joined: Apr 2020
@bumpo is right in his post #7, have to add third loop....

and "name" --> "nama" have to be put the last in the array for find and replace.

Tested on my Google Sheet:

CODE

function fandr() {
 var r=SpreadsheetApp.getActiveSheet().getDataRange();
 var rws=1;
 var count=6;
 var cls=r.getNumColumns();
 var i,j,k,a;
 var find = [];
 var repl = [];
 
 find[0]="nick name";
 repl[0]="nama susu";
 
 find[1]="first name";
 repl[1]="nama pertama";
 
 find[2]="last name";
 repl[2]="nama terakhir";
 
 find[3]="family name";
 repl[3]="nama keluarga";
 
 find[4]="age";
 repl[4]="umur";
 
 find[5]="name";
 repl[5]="nama";
 
 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;}
   
     for (k=0; k<count; k++)
     {
       try {
         a=a.replace(find[k],repl[k]);
         r.getCell(i, j).setValue(a);
       }
       catch (err) {continue;}
     }
   }
 }
}


Was modified from TS quoted script.
(Can expand the size of array, and adjust Count accordingly) wink.gif

before:
user posted image

run:
user posted image

after:
user posted image

TSk town shit
post Jun 6 2020, 09:57 AM

Enthusiast
*****
Junior Member
757 posts

Joined: Jan 2009
QUOTE(bumpo @ May 27 2020, 09:51 AM)
and all it takes is one internet outage and there goes all your productivity. its not the perfect solution for everyone  laugh.gif
*
There is an offline mode, we can enable it.
Tabasco
post Jul 1 2020, 10:40 AM

New Member
*
Junior Member
41 posts

Joined: Jan 2003
Sabahan guy is the original artist of the song...eh..


 

Change to:
| Lo-Fi Version
0.0135sec    0.30    5 queries    GZIP Disabled
Time is now: 28th March 2024 - 05:22 PM