Skip to main content
  • Home
  • Get Help
  • How Do I
  • Match pieces of string to fields in a separate Google Sheet and returning a different field if true
Question

Match pieces of string to fields in a separate Google Sheet and returning a different field if true

  • 12 October 2023
  • 4 replies
  • 56 views

Hello, we need a configuration that basically does this:

  1. we receive an email with text (the email comes from our LLM in a structured format)​​​​​​​
  2. we scan the email to find the tags
  3. we search for the appropriate link for each tag in a Google Sheet
  4. we send out ONE email with all the links

This is how far I have got:

From our LLM we get an email that looks like this basically:


Your Chat: Bla-Bla
Tags: A, B, C, D

 

From here I managed to parse the e-mail (Zapier Email Parser), grab the tags, split them, remove the Whitespaces (Zapier Formatter).

 

This is where it gets interesting. What I did was I created a zap with new multiple spreadsheet rows with each tag in its own field. Then I created another zap that runs when a new row is created. I used the Lookup Spreadsheet Row function, which worked great, but this means a person will get 4 emails in this example (4 tags).

 

I tried to do the Lookup in the first Zap, but if I Split the text using All-(As Line-Items) it searches for the entire string (A, B, C, D), not each field. I cannot Split the text into Separate Items as the number of tags is different for each chat… Any help would be great! :)

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @MonopolyTribe 

Good question.

To help us have more context, please post screenshots of these:

  • How your Zap steps are configured
  • How your GSheet is configured
  • A specific example with the provided inputs and desired outputs
⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
M

Hey Troy,

 

Thank you for your response! Will post screenshots below, just wanted to say I made progress in a different way, but still need help - I created a Google Docs as a step in the beginning, then a loop for the sorted data (works perfectly), which checks every piece for the appropriate link, then sends the link into the google sheets. Then I created a Filter Zap, where continue only if Loop Iteration Is Last (bool) is true. After that I created a send Gmail, but in the field of the body of the email, I cannot find the correct field, which shows the body of the Google Doc I created earlier. There are a ton of options, but none of them is Body/Content etc…

 

For an example, here is a chat history:

**********************************************************

Here is a copy of your session with VisaGeeza.Ai.

If you need any further assistance please Ask A Question at https://hongkongvisageeza.com/ask-us/.

To: hkvisacentre.com@gmail.com

Here is your chat:

Can I Hold a Second Job While in Hong Kong Working Under a Training Visa?

The Hong Kong training visa program allows foreign nationals or mainland Chinese residents to undergo a finite period of training in Hong Kong. In this comprehensive overview, we will cover the eligibility criteria, application process, approval considerations, maximum period of stay, dependent policy, and the concept of the Twilight Zone for visa applicants currently resident in Hong Kong.

Eligibility Criteria:
To be eligible for a Hong Kong training visa, the following criteria must be met:
- The applicant must not have any security objections or known records of serious crime.
- The applicant and the sponsoring company must have bona fides.
- The sponsoring company must be well-established and capable of providing the proposed training.
- A contract must be signed between the sponsoring company and the applicant.
- The sponsoring company must guarantee in writing the maintenance, repatriation, and training of the applicant.
- The proposed duration and content of the training program must be justified.

Application Process:
The application for a training visa can be submitted through various channels:
- Online submission to the Immigration Department (ImmD).
- Submission via post from overseas or in person to the Receipt & Dispatch Unit of the Immigration HQ.
- Inclusion as part of an application to Change Status or Visa Category if the applicant is already in Hong Kong as a resident.
- Submission via the nearest Chinese diplomatic mission in the applicant's country of domicile.

Approval Considerations:
The approvability of a training visa application depends on the following factors:
- Bona fides of the sponsoring entity providing the training.
- Unavailability of the training in the applicant's home country.
- Reasonable scope and length of the training in all circumstances.

Maximum Period of Stay:
The maximum period of stay for a training visa is 12 months, which is non-extendable. The actual period of stay is tied to the formal length of the training contract.

Dependent Policy:
Under the prevailing dependent policy, applicants admitted for training can bring their spouse, partner, or unmarried dependent children under 18 to Hong Kong. The trainee will be the sponsor for accompanying dependents. The dependent's length of stay is linked to the sponsor's, and dependents are required to leave Hong Kong when the sponsor leaves. Dependents are allowed to work in Hong Kong under existing policy.

The Twilight Zone:
The Twilight Zone refers to the period when an individual's residence visa in Hong Kong has expired, and they are waiting for their application for an extension or change of visa sponsor/category to be processed by the Immigration Department (ImmD). During this time, the individual does not have a valid period of stay endorsed in any official ImmD document, but they are allowed to remain in Hong Kong with the tacit consent of the Department. The Twilight Zone ends when the ImmD grants the new status and extension to the individual's current limit of stay. It is important to note that being in the Twilight Zone does not break the continuity of ordinary residence for the purposes of a right of abode application subsequently.

Can I Hold a Second Job While in Hong Kong Working Under a Training Visa?
According to the information provided in the context, there is no specific mention of whether a training visa holder can hold a second job in Hong Kong. To obtain accurate and up-to-date information on this matter, it is recommended to refer to the official sources such as the Hong Kong Visa Handbook (Click to learn more: [Hong Kong Visa Handbook](https://hongkongvisahandbook.com/)) or contact the Immigration Department directly.

In summary, the Hong Kong training visa program allows foreign nationals or mainland Chinese residents to undergo a finite period of training in Hong Kong. The eligibility criteria include security clearance, bona fides of the applicant and sponsoring company, and a well-established sponsoring company capable of providing the proposed training. The application can be submitted online, via post, or through diplomatic missions. The approval considerations focus on the sponsoring entity's bona fides, unavailability of training in the applicant's home country, and reasonable scope and length of training. The maximum period of stay is 12 months, and dependents can accompany the trainee under the prevailing dependent policy. The concept of the Twilight Zone refers to the period when an individual's residence visa has expired, and they are waiting for the processing of their extension or change of visa sponsor/category. For more detailed information and application forms, please refer to the Hong Kong Visa Handbook.

Tags: undergoing training, training visa, changing to training visa, form ID992A, form ID91, ImmD Extension Section, training activity, training visa approvability test

Sources:
- [Hong Kong Visa Handbook](https://hongkongvisahandbook.com/) (Click to learn more: [Hong Kong Visa Handbook](https://hongkongvisahandbook.com/))
- [Hong Kong Training Visa Checklist](https://www.hongkongvisahandbook.com/contents/Training/HongKongTrainingVisaChecklist.pdf)

Chat Ref: Rgf1vabx1OWkq93MIkfc

**********************************************************

 

From here I parse the email; extract the row/s after Tags; remove the blank rows, Whitespaces; then split into Line Items with a separator COMMA; then create a Spreadsheet with the Body (for other reasons that do not concern this activity); then start looking for a match from each tag with a Google Spreadsheet, where one column is the tag, the other one is a link; then send an email to the sender of the first email with some text like:

 

“Hey, NAME,

 

Here are some useful links:

LINK 1
LINK 2
LINK 3

 

Thank you in advance and have a great weekend :)

M

Here is what I mean by I cannot find the correct field in the Gmail Body:

 

Troy Tessalone
Userlevel 7
Badge +14

@MonopolyTribe 

Check this article:

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Terms & Conditions