I'm working within Google Sheets and passing information from multiple sheets to another. However, when the user inputs their Reps and Weight into a form (That then sends the information to Google Sheets), it places ALL of that info within a single cell.

So, cell "C2" will receive:

"Reps: 15, Weight: 150 Reps: 12, Weight: 175 Reps: 10, Weight: 200 Reps: 8, Weight: 225 Reps: 6, Weight: 250"

In the form of one single cell.

Using App Script, how can I dissect this single cell to have access to the numbers so I can place each one into a different corresponding cell?

I want to be able to move 15 to "C4" and 150 to "C5" and so on. I don't need any of the words etc, just the numbers saved into separate variables.

So far I have...

var Repsandweight = XPentry.getRange("M2").getValue(); 

var string = Repsandweight.toString(); 

var string2 = string.replace("Reps: ", ""); 

var string3 = string2.indexOf(", Weight: "); 

var Rep1 = string2.slice(0,string3); 

var string4 = string2.replace(Rep1   ", Weight: ", ""); 

var string5 = string4.indexOf("Reps: ", ""); 

var Weight1 = string4.slice(0,string5); 

It works, but is very, very slow and only iterates as many times as I tell it to. Basically, I'm replacing the words with nothing, locating the next word, snipping it before that word appears, saving the snippet as a variable. How can this be a loop that stops if the list is a different size?

CodePudding user response：

You can just parse the string with regex to get the values you need:

Within the loop, you'll have access to the reps and weight as strings, which you can use for inserting into whichever cell you want.

const fullString = "Reps: 15, Weight: 150 Reps: 12, Weight: 175 Reps: 10, Weight: 200 Reps: 8, Weight: 225 Reps: 6, Weight: 250";

const matches = fullString.matchAll(/Reps: (\d ), Weight: (\d )/g);

for (const match of matches) {
  const [reps, weight] = match.slice(1);
  console.log('Reps:', reps);
  console.log('Weight:', weight);
}