Collecting Beta Testers and Cleaning TestFlight CSVs

Collect testers with Google Sheets then clean them with my tool

First up, Andrew’s form was set up with two fields: name and email.

TestFlight requires a CSV with three columns: first name, last name, and email.

The data needs to be converted first.

To convert the two-column data to the three-column CSV, we first created a new sheet. We placed headers at the top for our own reference, First Name, Last Name, and Email, then created a formula to extract each part from the first sheet. The first sheet, named “Testers”, contained all the responses from the form. To split the name, we started by using the SEARCH function, the Google Sheet/Excel equivalent of index /indexOf/index(where:), to check if there was a space in the name that we could use to split: SEARCH(" ", Testers!$B2). If there was, the formula used the LEFT function to create a substring up to that space: LEFT(Testers!$B2, SEARCH(" ", Testers!$B2)). If not, it returned the original content of the cell. The full formula for the first name column is as follows.

The last name column has a similar formula, with two differences. The first is that the RIGHT function is used instead of LEFT. RIGHT returns a substring starting from the end of the string. Because the RIGHT function is used, the SEARCH call inside it has to be subtracted from the total length of the string: LEN(Testers!$B2) - SEARCH(" ", Testers!$B2). The full formula for the last name column is below.

The email formula is very simple, just a reference to the cell in the responses sheet: =Testers!$C2. The three formulas are copied to all the rows in the sheet.

The first step is to turn the raw data into a two-dimensional array. The CSV text was split using the newline character \n, then each row was split using a comma as a delimiter. This output was stored in another useState hook.

The third step is to clean the CSV using a reducer. There are two settings the user can apply that affect this function: specifying the first row as the header row, and leaving malformed/duplicated rows in the preview.

The first part of this step is to check if the email has appeared in any preceding rows. If it has and the leave duplicated rows setting is active, it outputs a “duplicate” flag, and adds it to the array. If it has and the setting is inactive, it returns the existing array.

The next part is to check for malformed emails, achieved by performing a similar check as step two. If the leave malformed rows setting is active, it outputs a “malformed” flag, then adds it to the array. If neither of these parts are triggered, the reducer does the regular invalid character cleaning.

The final step is to make the preview. It loops over the two-dimensional cleaned CSV data to create a table, and if a malformed or duplicate flag is set, it places a colour on the email. Red signifies malformed and yellow signifies duplicated.

Related posts:

Rebel Token is a deflationary token with token mechanics that stabilizes the token sales by incorporating a buy-back approach by automatic buy-back code in the smart contract. Rebel Token collects 5%…

When preparing a check out to the stunning Gold Coast, it’s necessary to consider your transport options, especially airport transfers. Going with the right airport transfer solution can save you…

My father would always tell me that if I wanted to get anything I wanted in life, that I would have to focus on the details while using the overall goal as the motivating force. By that he meant to…