CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-cloud-firestore
Ciaran Crowley
Ciaran Crowley

Reputation: 419

Loop through cells in Google Sheet and remove a subString of text

I need to loop through each populated cell in my Google Sheet and remove these characters: {stringValue=}.

One of my columns looks like this:

COLUMN NAME: category
{stringValue=Pump}
{stringValue=Motor}
{stringValue=Motor}
{stringValue=Flow Meter}
{stringValue=Flow Meter}
{stringValue=Flow Meter}
{stringValue=Motor}
{stringValue=Flow Meter}
{stringValue=Pump}
{stringValue=Motor}
{stringValue=Pump}

I need to keep text such as Motor or Pump.

This is how I populate the sheet:

function importData() {
  const firestore = getFirestore();

  const allDocuments = firestore.getDocuments('Data').map(function(document) {
    return document.fields;
  });

  const first = allDocuments[0];
  const columns = Object.keys(first);

  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sheet.getActiveSheet();
  ss.clear();
  sheet.appendRow(columns);

  allDocuments.forEach(function(document) {
    const row = columns.map(function(column) {
      return document[column];
    });
    sheet.appendRow(row);
  });
}

Upvotes: 1

Views: 321

Answers (3)

Tanaike
Tanaike

Reputation: 201513

I believe your goal as follows.

  • You want to achieve the following situation using Google Apps Script.. These values are put in a column.

    • From

        COLUMN NAME: category
  {stringValue=Pump}
  {stringValue=Motor}
  {stringValue=Motor}
  {stringValue=Flow Meter}
  {stringValue=Flow Meter}
  {stringValue=Flow Meter}
  {stringValue=Motor}
  {stringValue=Flow Meter}
  {stringValue=Pump}
  {stringValue=Motor}
  {stringValue=Pump}

    • To

        COLUMN NAME: category
  Pump
  Motor
  Motor
  Flow Meter
  Flow Meter
  Flow Meter
  Motor
  Flow Meter
  Pump
  Motor
  Pump

In this case, I would like to propose to use TextFinder. I thought that when the TextFinder is used, your goal can be achieve your goal with the low process cost. The sample script is as follows.

Sample script:

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const columnNumber = 1; // Please set the column number. 1 is the column "A".

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet
    .getRange(2, columnNumber, sheet.getLastRow() - 1, 1)
    .createTextFinder("\\{stringValue\\=(.+)\\}")
    .useRegularExpression(true)
    .replaceAllWith("$1");
}

  • If you want to replace for the various stringValue, I think that you can also use \\{.+\\=(.+)\\} instead of \\{stringValue\\=(.+)\\}.

  • About your new question of How would you clear any cells that only contain {stringValue=} when there is no data in that field?, in this case, how about modifying the regex in createTextFinder from \\{stringValue\\=(.+)\\} to \\{stringValue\\=(.+|)\\}? By this, the value of {stringValue=} is replaced with the empty.

Result:

When above script is used, the following situation is obtained.

From:

enter image description here

To:

enter image description here

References:

Upvotes: 1

Erik Tyler
Erik Tyler

Reputation: 9355

You could also do this with Find/Replace:

1.) Select the entire target column.

2.) Hit Ctrl-H to open the Find/Replace dialog.

3.) In the "Find" field enter the following: ^.+=(.+).

4.) In the "Replace with" field enter the following: $1

5.) Check the box that says "Search using regular expressions."

6.) Click the button that says "Replace All."

The whole process takes about 10 seconds.

Upvotes: 0

JohnA
JohnA

Reputation: 1107

This can be done using a script but also can be done with a formula as follows:

=LEFT(MID(A18,FIND("=",A18)+1,100),LEN(MID(A18,FIND("=",A18)+1,100))-1)

Now it seems the "=" is always at position 13, if so, the FIND() function can be removed and you can hard code 13 to simplify the function.

enter image description here

You can copy this formula down as needed or use an array formula instead and it will populate itself as needed:

=ARRAYFORMULA(IF(A18:A <> "",LEFT(MID(A18:A,FIND("=",A18:A)+1,100),LEN(MID(A18:A,FIND("=",A18:A)+1,100))-1),""))

Upvotes: 0

Related Questions