Google Apps Script that processes each row in a Google Sheet checking the last part of a string in a specific column

To create a Google Apps Script that processes each row in a Google Sheet, checking the last part of a string in a specific column, and if that last part is all numeric, splits it off and copies it to the next column, follow these steps. This script assumes you want to check and process data starting from the second row (assuming the first row contains headers) and in a specific column (for example, column A).

Open your Google Sheet: Go to the Google Sheets document you want to work with.
Open Script Editor: In the Google Sheets menu, click on Extensions > Apps Script.
Replace the code in the Script Editor: Delete any code that’s already there and replace it with the script provided below. This script reads each row in the specified column, checks if the last part of the string (after the last space) is numeric, and if so, moves that numeric part to the next column.

function processRowsAndSplitNumbers() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange(); // Adjust this if you want to limit the range
  const values = range.getValues();

  // Loop through each row in the column
  for (let i = 1; i < values.length; i++) { // Start from 1 to skip header row
    const row = values[i];
    const cellValue = row[0]; // Assuming we're checking column A
    const parts = cellValue.split(' '); // Split the string by space
    const lastPart = parts[parts.length - 1]; // Get the last part of the split

    // Check if the last part is numeric
    if (!isNaN(lastPart) && lastPart.trim() !== '') {
      // If numeric, remove the last part from the original string
      parts.pop(); // Remove the last element (the numeric part)
      const newStringValue = parts.join(' '); // Re-join the remaining parts
      const numericValue = lastPart; // The numeric part to move to the next column

      // Update the sheet: set the new string value back to the original cell
      // and the numeric value to the next column
      sheet.getRange(i + 1, 1).setValue(newStringValue); // +1 because ranges are 1-indexed
      sheet.getRange(i + 1, 2).setValue(numericValue); // Assuming you want to put the number in column B
    }
    // If the last part is not numeric, the row is skipped automatically
  }
}

Save and name your project: Click on File > Save, and give your project a name.
Run the script: Click on the play (▶) button next to the processRowsAndSplitNumbers function to execute the script. You will likely need to authorize the script to run under your Google account the first time by following the prompts.

This script operates on the premise that your data starts in row 2 (to skip a header row in row 1) and that you’re examining and potentially modifying the data in column A. Adjust the column indexes in the script (row[0] for column A and the next column with sheet.getRange(i + 1, 2)) as necessary to match your specific needs.

Always test scripts like these on a copy of your data to prevent unintended data loss or changes.

Share this:

Related