4

Use This Script to Separate the First and Last names in Google Sheets

 1 year ago
source link: https://hackernoon.com/use-this-script-to-separate-the-first-and-last-names-in-google-sheets
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

@kcl

Khadka's Coding Lounge.

As a freelancer, I create addons and scripts to automate ...


Receive Stories from @kcl


Credibility

Would you like to separate the column in your spreadsheet that consists of the full names of people into first and last names? Well then, stick this very simple script to your spreadsheets apps script editor and you'll be able to do just that.

Split The Selected Column

The following bound script will do three things:

  1. Create a custom menu in your spreadsheets tabs with the title Custom Menu.

  2. After you call select the custom menu, It will check the cell you've selected to split into. The script won't work if you don't select a cell in the right column.

  3. Separate the whole column into two columns, with the first value in the first column and the second(& rest if there are any) in the new column.

    function splitName() {
    // get sheet and data
    const sheet = SpreadsheetApp.getActiveSheet();
    // get selected row
    const activeColIndex = sheet.getActiveRange().getColumn();
    
    const data = sheet.getDataRange().getValues();
    const lastRow = data.length;
    
    sheet.getRange(1,activeColIndex,lastRow,1).splitTextToColumns();
    
    }
    
    // if you're new and only want this feature the use this code
    /**
    
    OnOpen trigger that creates menu
    
    @param {Dictionary} e
    */
    function onOpen(e) {
    createCustomMenu();
    }
    
    /**
    
    Menu creates menu UI in spreadsheet.
    */
    function createCustomMenu() {
    let menu = SpreadsheetApp.getUi().createMenu("Custom Menu"); // Or DocumentApp or SlidesApp or FormApp.
    
    menu.addItem("Split Names", "splitName");
    menu.addToUi();
    }
    
    /**
    
    Code By Nibesh Khadka.
    
    I am freelance and Google Workspace Automation Expert.
    
    You can find me on:
    
    https://linkedin.com/in/nibesh-khadka
    
    https://nibeshkhadka.com
    
    [email protected]
    */
    

How To Add Apps Script Code To a Spreadsheet

If you don't know how to add this script to your sheet, then just click the Extensions tab and then Apps Script as shown in the image below.

Open Script Editor

Now, similar to the previous blogs, you can now just:

  1. Save the code.
  2. Reload the document. Where you'll see the custom menu as shown below
  3. And execute the function.

Executing The Function

Here are a few images to guide what the operation will look like in your docs.

Custom Menu

Before and After Splitting Names

Thank You for Your Time

Don’t forget to like and share this blog.

Also published here.


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK