Reformatting Data before sending it on to Google Sheets

[TimMan]

Hi Folks…

I have data coming from a webhook (Formaloo is the original form) and I need to reformat it before sending it on to Google Sheets.

Here’s two examples…

1. The incoming text data is the word “Transportation” but I need it to say “ID:3”
2. The incoming text data is the word “Housing” but I need it to say “ID:4”

Is Data Mapper what I should be using?

And if so, are there any tutorials on this somewhere?

Thanks,

Tim

[ashrafsam]

You might need a Code step for this purpose, the data mapper might be too simple for this use case.

Are you reformatting only one question’s answer or multiple fields? How many of these categories do you have? Are they static?

[TimMan]

Hi Ashrafsam!

I messed around with Code for a few hours last night to see if I could make it work, but unfortunately, I know absolutely nothing about Python or Typescript. I tried and failed.

I did have success using a text reformatter in a different product, as it was a simple point and click. But I would much prefer to learn how to do it in AP and use that!

I have a series of questions in the original form, but only one of them needs to be reformatted. It is a required field, so there will never be a data set without this Category Field.

Here is the Original Text (Category) from Form/Webhook followed by the Numerical Value needed in the Google Sheet.

Do you think this is doable in AP?

Housing - 2
Transportation - 3
Healthcare - 4
Culture - 5
Visa & Immigration - 6
Financial - 7
Food - 8
Tourism - 9
Home Services - 10
ExPat Advisor - 14

Thanks for the reply!

Tim Martin

[TimMan]

I’ve been doing some reading and I almost bet that I need to use Code to reformat and then Storage to hold the data before sending …

[TimMan]

Hi @ashrafsam

I did a bunch of reading and finally got into the Open ai help bot on the AP help pages. Here’s what I came up with…do you think any of this is helpful advice?

---

My Question - Please write the appropriate code to paste into the Code Piece to replace three example words with three example replacement words

Certainly, in the Code Piece, the task to replace words can be accomplished in JavaScript using the replace() function. Below is the code for replacing three example words, ‘word1’, ‘word2’, and ‘word3’, with the replacement words ‘replacement1’, ‘replacement2’, and ‘replacement3’, respectively:

function main({inputs}) {
  let text = inputs.text;

  const replacements = {
    'word1': 'replacement1',
    'word2': 'replacement2',
    'word3': 'replacement3'
  };

  for (let word in replacements) {
    let replacement = replacements[word];
    text = text.replace(new RegExp(word, 'g'), replacement);
  }

  return {
    text: text
  };
}

In this example, the inputs argument is a JavaScript object with a property text, which is the string that you want to transform. The replacements object maps the original words to their replacements. The for...in loop iterates over the properties (i.e., words to replace) in the replacements object.

Finally, for each word to be replaced, the code uses the replace method to replace all occurrences of the word (designated by the ‘g’ flag in RegExp(word, 'g')) with its replacement.

To use this in Activepieces, you need to paste this code into the ‘Code’ section of the Code Piece. Then, in the Parameters section, add a parameter named ‘text’ and input the text in which you want to replace words.

---

I tried my best to insert the code, add parameters, etc etc for hours. I just kept failing. Any ideas?

TimMan

[kishanprmr]

Hi @TimMan ,
You can use below code to reformat Category .

export const code = async (inputs) => {
    const MAPPING: { [key: string]: number } = {
        "Housing": 2,
        "Transportation": 3,
        "Healthcare": 4,
        "Culture": 5,
        "Visa & Immigration": 6,
        "Financial": 7,
        "Food": 8,
        "Tourism": 9,
        "Home Services": 10,
        "ExPat Advisor": 14
    };
    const inputCategory = inputs.category;
    if(MAPPING[inputCategory]){
        return MAPPING[inputCategory];
    }
    else{
        return inputCategory;
    }
};

Also make sure you give Category field into inputs for Code piece.

Here I have given name category for key, please use same key name. On value column , you can map Category from webhook step.

In the output of code piece, you will receive formatted value of Category. (i.e. 2,10,6).

Let me know if you are facing any error in this code.

[TimMan]

Thank you @kishanprmr !

I’m so excited…I will try this out right away and let you know!

TimMan

[TimMan]

Thankyou @kishanprmr !!!

This is the GREATEST!

The code worked perfectly. It only took me a few minutes to read and understand, so I could follow your instructions perfectly.

I am so thankful for your help.

I have another question regarding the Code Piece and splitting a Name Field into First and Last, but I will ask that in a new thread.

Thank you so much!

TimMan

[kishanprmr]

@TimMan

The code worked perfectly. It only took me a few minutes to read and understand, so I could follow your instructions perfectly.

I’m glad to hear that things are working now.

@TimMan

I have another question regarding the Code Piece and splitting a Name Field into First and Last, but I will ask that in a new thread.

You can use Text Helper piece. It has Split action to split the text content.
If you face any error in that piece, feel free to open new thread.