Google Sheets – Find and Replace multiple Code

• You want to replace the Emojis to the defined Latex commands.
  • For example, you want to replace as follows.
  • From 03.01.19, 00:29 - me: Hi 😊 to 03.01.19, 00:29 - me: Hi \nettnett.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

The flow of this sample script is as follows. This sample script uses your shared Spreadsheet.

1. Retrieve data from the sheet of Codes for Smileys.
2. Create the request body for the findReplace request of batchUpdate method of Sheets API.
3. Run the method of batchUpdate.

Sample script:

This script used Sheets API. So, before you run the script, please enable Sheets API at Advanced Google services.

function myFunction() {
  var dataSheet = "Codes for Smileys";
  var sourceSheet = "unedited Chats with Smileys";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName(dataSheet).getDataRange().getValues();
  data.shift();
  var sheetId = ss.getSheetByName(sourceSheet).getSheetId();
  var requests = data.map(function(row) {return {findReplace: {sheetId: sheetId, find: row[0], replacement: row[1]}}});
  Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
}

• This sample script uses your shared Spreadsheet. So in this case, the data sheet is Codes for Smileys. And the source sheet for converting is unedited Chats with Smileys.

Note:

• If you change the sheet name, also please modify above script. Please be careful this.
• When you run the script for the first time, the authorization screen is opened. So please authorize the scopes for using the script.

References:

• Advanced Google services
• spreadsheets.batchUpdate
• FindReplaceRequest

Added:

• You want to put the converted values to the sheet of Chat with Latex Code.

The sample script for achieving above is as follows.

Sample script:

function myFunction2() {
  var dataSheet = "Codes for Smileys";
  var sourceSheet = "unedited Chats with Smileys";
  var destinationSheet = "Chats with LaTeX Codes";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(dataSheet);
  var data = ss.getSheetByName(dataSheet).getDataRange().getValues();
  data.shift();
  var srcSheet = ss.getSheetByName(sourceSheet);
  var tempSheet = srcSheet.copyTo(ss);
  var sheetId = tempSheet.getSheetId();
  var requests = data.map(function(row) {return {findReplace: {sheetId: sheetId, find: row[0], replacement: row[1]}}});
  Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
  var tempValues = tempSheet.getDataRange().getValues();
  var destSheet = ss.getSheetByName(destinationSheet);
  destSheet.getRange(destSheet.getLastRow() + 1, 1, tempValues.length, tempValues[0].length).setValues(tempValues);
  ss.deleteSheet(tempSheet);
}

• In this sample script, the following flow is run.
  1. Copy the source sheet unedited Chats with Smileys as a temporal sheet.
  2. Create request body for the batchUpdate method to the temporal sheet.
  3. Run the batchUpdate.
  4. Copy the converted values from the temporal sheet to the destination sheet Chats with LaTeX Codes.
     • In this case, the converted values are put to the last row of the sheet.
  5. Delete the temporal sheet.