Teacher Paul
Teaching, Living and Learning in Hanoi
  • Twitter
  • Google+
RSS
  • Home
  • About me
    • Bio
    • Philosophies and Beliefs
  • Resume
    • Full Resume
    • Job Description
    • Accomplishments
  • Presentations
    • 21st Century Problems
    • Digital Wisdom
    • Google Sheets – Feel the Power
    • Google Scripts – Ready, Set, Script
    • Google Drive – Institutional Level
    • Libraries and Learning Commons
    • SAMR in 60 Minutes
    • SAMR in Action
  • Classes Taught
    • Cultural Arts of the World
      • Arts of China and Tibet
      • Arts of East Africa
      • Arts of Korea and Japan
      • Arts of Middle East and India
      • Arts of West Africa
    • American Popular Music
    • Traditions of Music
    • Ethics Through the Lens of 9-11
    • Critical Comedy
  • Websites Built
    • Virtual Trips
    • Map Everything
    • UNIS Tech Connect
    • Vietnam Tech Conf 2014
    • Harmony School
  • Code, Writings, Reflections
    • Blog
    • Google Scripts
    • Tutorials
    • Honors Dissertation
    • Ethnomusicology Research

Jun 2 2017

Way of the Ninja Pirate

By Paul Swanson • Blog • 0

Nov 5 2016

Google Sheets Function: unsign Vietnamese text

I recently received a request from someone at Hanoi Medical University to create a script that would ‘unsign’ Vietnamese text in a Google Sheet. They are using Google Sheets to automatically create email addresses for people who work there, and they were running into a problem with all of the accents, tones and signs that are common in Vietnamese.

For example:

“Bùi Thị Kim” needed to be rendered as an email: “buithikim@hmu.edu.vn”

They sent me a script in VBA that works with Excel, and I just converted it over to Google Scripts.

To use this, just open up Google Sheets, click Tools, Script Editor, delete whatever you see in the template and paste in the code below.

The basic use of the function is “=ConvertToUnSign(string)”. If you want to do some additional modifications to make it into an email like the one above, try this function:

“=CONCATENATE(SUBSTITUTE(LOWER(ConvertToUnSign(C2)),” “,””),”@hmu.edu.vn”)”

If you find it helpful, let me know in the comment section below!

function ConvertToUnSign(sContent) {
  var charArray = [];
  var unSignCharArray = [];
  for (i=0; i<sContent.length; i++) {
    charArray[i] = sContent.charCodeAt(i);
    unSignCharArray[i] = String.fromCharCode(removeSign(charArray[i]));
  }
  return (unSignCharArray.join(""));
}

function removeSign(char) {
  switch (char)  
  {
    case 273:  // convert to 'd' 
      char = 100;
      break;
    
    case 272: // convert to 'D'
      char = 68;
      break;
    
    case 224: case 225: case 226: case 227: case 259: case 7841: case 7843: case 7845: case 7847: case 7849: case 7851: case 7853: case 7855: case 7857: case 7859: case 7861: case 7863: 
    // convert to "a"
      char = 97;
      break;
    
    case 192: case 193: case 194: case 195: case 258: case 7840: case 7842: case 7844: case 7846: case 7848: case 7850: case 7852: case 7854: case 7856: case 7858: case 7860: case 7862: 
    // convert to “A”
      char = 65;
      break;
    
    case 232: case 233: case 234: case 7865: case 7867: case 7869: case 7871: case 7873: case 7875: case 7877: case 7879: 
    // convert to “e”
      char = 101;
      break;
        
    case 200: case 201: case 202: case 7864: case 7866: case 7868: case 7870: case 7872: case 7874: case 7876: case 7878: 
    // convert to “E”
      char = 69;
      break;
        
    case 236: case 237: case 297: case 7881: case 7883: 
    // convert to “i”
      char = 105;
      break;
       
    case 204: case 205: case 296: case 7880: case 7882: 
    // convert & “I”
      char = 73;
      break;
        
    case 242: case 243: case 244: case 245: case 417: case 7885: case 7887: case 7889: case 7891: case 7893: case 7895: case 7897: case 7899: case 7901: case 7903: case 7905: case 7907: 
    // convert to “o”
      char = 111;
      break;
        
    case 210: case 211: case 212: case 213: case 416: case 7884: case 7886: case 7888: case 7890: case 7892: case 7894: case 7896: case 7898: case 7900: case 7902: case 7904: case 7906: 
    // convert to “O”
      char = 79;
      break;
        
    case 249: case 250: case 361: case 432: case 7909: case 7911: case 7913: case 7915: case 7917: case 7919: case 7921: 
    // convert to “u”
      char = 117;
      break;
        
    case 217: case 218: case 360: case 431: case 7908: case 7910: case 7912: case 7914: case 7916: case 7918: case 7920: 
    // convert to “U”
      char = 85;
      break;
        
    case 253: case 7923: case 7925: case 7927: case 7929: 
    // convert to “y”
      char = 121;
      break;
        
    case 221: case 7922: case 7924: case 7926: case 7928: 
    // convert to “Y”
      char = 89;
      break;
    
  }
  return char;
}

By Paul Swanson • Google Script • 0

Nov 21 2015

Final Rendering

It feels good to be running the final rendering of the video, and also to be wrapping up the final project of Course Five of COETAIL.

Without a doubt, it’s been a challenging project. I began wanting to look at the issue of Tech Literacy across the whole middle school, but Kim wisely steered me towards a more specific project: our Grade 8 Digital Citizenship course in Humanities. I had taught some lessons related to this in the past, I was familiar with the course material; how hard could it be?

I quickly realized the difference between knowing and preparing material to teach yourself, and helping coach a team of three teachers with their own unique strengths and styles. At some points, I felt like I almost needed to develop three different units for each of the teachers so that they could all play to their strengths.

Screen Shot 2015-11-21 at 6.03.45 PMWe met, either one-on-one or in groups of three or four, as often as we could. I also used emails, Google Docs, comments, Google+. I tweeted out questions and asked for advice on COETAIL’s Google+ Community, and did a Hangout with a fellow COETAILer to discuss our projects.

I had some ideas for a final summative task, but the teachers were looking at some of the Digital Citizenship curriculum at Yokohama International School and decided that they wanted to borrow their idea: the G08 students would focus on a specific topic and then develop a lesson to teach that topic to G06 students.

My work really fell into three different parts:

  • Helping the teachers identify and wrestle with some of the issues involved with Digital Citizenship
  • Developing preliminary lessons that would cover the 4 areas of Digital Citizenship that they identified:
    • Online Presence and Persona
    • Online Safety and Privacy
    • Community Engagement
    • Ethics and Responsibility
  • Take the Summative Task and push it a bit further so that it reaches the level of Redefinition

As the unit wound down, I did an interview with Adam and Lauren so that I could both get feedback from them and also bring some of their words into my final video project. I tried to find time to do an interview with Sandra as well, but we ran into scheduling issues and then suddenly we were out of time.

For the final video, I used iMovie more or less for the first time. I have done some little projects with it before, but nothing as complex as this. I found it very helpful to put my titles and photos into a Google Slide presentation and then download each slide as a JPEG to be imported into iMovie. That saved me the trouble of resizing and adjusting everything within iMovie itself.

So now, without further ado, the movie itself:

By pswanson • Blog • 2

< 1 2 3 4 5 >»

Google Training

My Homes

Latest Tweets

  • So excited to share what @WyattBiessel @wanderingnoah @brianinskeep and the MS/HS teachers and students have been d… t.co/qeCarUr2mb Time ago 14 Hours via Twitter Web App Reply - Retweet - Favorite
  • RT @Malala: ‘For there is always light, If only we’re brave enough to see it If only we’re brave enough to be it’ @TheAmandaGorman https:/… Time ago 3 Days via Twitter for iPhone Reply - Retweet - Favorite

Follow @teachertechpaul on twitter.

Recent Posts

  • Digital Learning: Metaphor and Meaning
  • Power of Questions
  • Digital Distinctions
  • Way of the Ninja Pirate
  • Google Sheets Function: unsign Vietnamese text

Recent Comments

  • pswanson on Google Sheets Lesson 7 – INDEX & MATCH
  • Geert Allard on Google Sheets Lesson 7 – INDEX & MATCH
  • A SMAR(T) Tool? Blog #3 – James Hardy's Blog on Rethinking SAMR
  • Theoretical learning models that support mobile devices – Learning and Teaching Enhancement on Rethinking SAMR
  • Community Engagement – Depth and Breadth – Teacher Paul on Rethinking SAMR

Archives

  • July 2019
  • March 2019
  • August 2017
  • June 2017
  • November 2016
  • November 2015
  • October 2015
  • September 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • June 2014

↑

© Teacher Paul 2021
Powered by WordPress • Themify WordPress Themes