Find and replace in a Google Sheet

The following Google Apps Script is designed to search Google Sheet data for a list of terms that need correcting and the cell colour changing on ones that have been modified. So here is a list of the 'incorrect' words to find and what it should be replaced by, as an example:

• xray change to X-Ray
• 1st change to First
• 2nd change to Second

The discrepancies may have occured from the longevity of the data and/or individual users having their own terminology. So we want to take a list of these words and in this example target a specific column of raw data where they may be present.

A list of words to find and their replacement equivalent.

Extract a list of Google Group members into a Sheet

The following Google Apps Script is designed to extract a list of the current members of a Google Group (email address and role) into a Google Sheet. You simply enter the email address of the Google Group that you belong to and then run the Function.

The script starts by using the 'GroupsApp' to access the Group and then get a list of all the users:

var group = GroupsApp.getGroupByEmail('YOUR GROUP EMAIL ADDRESS HERE');
var members = group.getUsers();

Once we have an array (list) of all the members we need to cycle through each one and get their email address and role, as well as tidying up some of the formatting (eg setting everything to lowercase). Then we can push this detail into a new array (list) that we will later use to paste into our Sheet:

for (var i=0; i<membersLength; i++) {
      
   // get Email Address
   var memberEmailAddress = members[i].getEmail();
      
   // get Role and set as lowercase
   var memberRole = group.getRole(memberEmailAddress).toString().toLowerCase();
      
   // push details into array for later pasting into Google Sheet
   memberDetails.push([memberEmailAddress, memberRole]);
      
}