Google Sheets – Find and Replace multiple Code

1
votes

Maybe someone can help me with my Problem.

First some background: I have copied some WhatsApp chats to Google Sheets. I use Latex to generate a book containing the chats. Google Sheet is able to display all Emojis from WhatsApp, Latex, of course, isn't. So I downloaded the Emojis as png files and defined Latex commands to include those emojis as graphic. For Example: Too display the regular smiling emoji I type \grin. I have a list with hundreds of emojis in one row and the corresponding command in the next row. Until now I used search and replace -> replace all in the same sheet for every single type of emoji. But as this takes hours I wondered if there is any way to make this more effective.

Here is a spreadsheet with a small example: Google Sheet

Thanks in advance!

enter image description here

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-api
Where are you searching and replacing against? The same spreadsheet? - IMTheNachoMan
@IMTheNachoMan yes, I added this information in the post - simande
In order to correctly understand your question, can you provide a sample Spreadsheet? Of course, please remove your personal information. - Tanaike
@Tanaike i made a short conversation with a chat, how it is supposed to look afterwards. you can find it here: docs.google.com/spreadsheets/d/… - simande
Thank you for replying and sharing the sample Spreadsheet. From your shared Spreadsheet, I proposed a sample script as answer. Could you please confirm it? If I misunderstood your question and that was not the result you want, I apologize. - Tanaike

1 Answers

2
votes
  • 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:

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.