Google-sheets – Automatically “split text to column” using Google App Script

google sheetsgoogle-apps-script

I'm trying to figure out "if" I can automatically "split text to column" using Google App Script and the trigger of "on Form Submit". If yes, can someone help me figure out the solution to following problem? If no, can someone please help me figure out an alternate solution?

I have a google form that is being used to enroll in professional development workshops. The workshop name being submitted via a google form has a Unique ID# in it. I need to copy or separate that unique ID# into another column.

Sample Workshop Text (Every workshop will be in this format)

Chromebook 101 | GHS | 8:00 am | ID#100103

The above string will be in cell B1 and I need to automatically copy or separate the value ID#100103 into cell C1. We will always be able to target the ID# as the place where the separation happens.

I know how to do this manually using the "data>split text into column", but I need this to be done automatically on form submission.

Any ideas?

Best Answer

You don't need a script for this. Enter the following in C1:

=arrayformula(iferror(regexextract(B1:B, "ID#\d+")))

This will populate column C with Id numbers (where the data in B is available), leaving the rest blank. As more entries are submitted through the form, more Id numbers will be in column C.

Another version uses a second regexextract to drop the ID# part and keep only the digits:

 =arrayformula(iferror(regexextract(regexextract(B1:B, "ID#\d+"), "\d+")))

Short answer

Is there a script, or some other solution, that will auto-Allow Access in a spreadsheet?

The only solution is that the owner or any editor do the mandatory authorization for each of the external spreadsheets.

Explanation

Google Apps Script doesn't include any class or method for Google Sheets built-in functions, so the authorization should be done on the Google Sheets UI by the owner or an editor of the spreadsheet.

The good news is that the access authorization is required only one time by external spreadsheet, so you could add a sheet having something like =IMPORTRANGE(url,"A1") for each spreadsheet, authorize the access to each one, and after that delete that sheet.

Note: The user who granted the permission should not be removed from the sharing settings.

References

IMPORTRANGE

Best Answer

Related Solutions

Google-sheets – Split content with multiple lines into multiple rows and columns in Google sheets

Google-sheets – Automatically “Allow Access” for IMPORTRANGEs in Google Sheets

Short answer

Explanation

References

Related Topic