在Sheets/App Script中按非固定规则拆分字符串至单元格的方法

Hey there! Let's work through this problem together—sounds like your old lastIndexOf trick worked okay for fixed-length substrings, but it got way too clunky when dealing with variable lengths, right? Here are some cleaner, more flexible strategies to handle extracting variable-length substrings, reordering them, and picking only what you need for your cells:

Regex is a total powerhouse for variable-length substring tasks. You can first match all the target substrings you care about from the original string, then reorder and filter them however you want before populating your cells. Let's use a common key-value string format as an example:

const originalStr = "[Name:Alice][Age:30][City:New York]";
// Regex to capture all key-value pairs wrapped in brackets
const regexPattern = /\[([^:]+):([^\]]+)\]/g;
const matchedPairs = [];
let currentMatch;

// Extract all matches into an array of objects
while ((currentMatch = regexPattern.exec(originalStr)) !== null) {
  matchedPairs.push({ key: currentMatch[1], value: currentMatch[2] });
}

// Filter and reorder: say we want City first, then Name
const selectedValues = [
  matchedPairs.find(pair => pair.key === 'City').value,
  matchedPairs.find(pair => pair.key === 'Name').value
];

// Now drop these into your cells (example spreadsheet logic)
selectedValues.forEach((val, index) => {
  // sheet.setCell(index + 1, 1, val);
});

The best part here is you don't have to manually calculate character positions—regex does the heavy lifting of finding all relevant substrings. Rearranging or picking different subsets later is just a matter of adjusting the selection logic.

If your string uses consistent delimiters (like commas, pipes, or colons), splitting first then converting to a key-value map makes things super straightforward:

original_str = "Product:Laptop,Price:999.99,Category:Electronics,Stock:15"
# Split into individual key-value parts
raw_parts = original_str.split(',')
# Convert to a dictionary for easy lookup
part_map = {}
for part in raw_parts:
    key, value = part.split(':')
    part_map[key] = value

# Define your desired order and extract values
target_order = ['Category', 'Product', 'Price']
cell_values = [part_map[key] for key in target_order]

# Now assign these values to your cells

This approach is way more readable than chaining lastIndexOf calls, and it's easy to tweak if your delimiters or desired order change.

If your string has a unique, non-standard structure (no obvious delimiters but a predictable pattern), wrapping the parsing, filtering, and reordering into a single function keeps your code clean and reusable:

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class StringParser {
    public static List<String> parseAndReorder(String originalStr, List<String> targetKeys) {
        Map<String, String> keyValueMap = new HashMap<>();
        
        // Add your custom parsing logic here based on your string's format
        // Example: extract keys and values from a structured string
        // ...
        
        // Build the result list in the desired order
        List<String> cellValues = new ArrayList<>();
        for (String key : targetKeys) {
            if (keyValueMap.containsKey(key)) {
                cellValues.add(keyValueMap.get(key));
            }
        }
        return cellValues;
    }

    // Usage example
    public static void main(String[] args) {
        String originalStr = "Your custom formatted string here";
        List<String> targetOrder = List.of("City", "Name");
        List<String> valuesForCells = parseAndReorder(originalStr, targetOrder);
        
        // Assign values to cells here
    }
}

Once this function is set up, you just pass in the original string and your desired order, and you get exactly what you need for your cells. Any future changes to the string format only require updating the parsing logic inside the function.

Quick Recap #

All these methods share a core idea: convert the raw string into structured data (arrays, maps, objects) first, then manipulate that data to get the order and subset you want. This is way more maintainable than manually tracking positions with lastIndexOf, especially when substring lengths are variable.

内容的提问来源于stack exchange，提问作者limonvnx