0

Hi I have a wordpress form, linked to a google sheet that transfers over entries as they come in, now I have some formulas applied, but I don't want to have to add the formula to each new row that comes in, what can I do to automatically, have my formula applied as entries come in via the WordPress form?

So in summary have the formula to apply its self as the entry comes in.

Improve this question
5
  • Please add more details like a sample data, the formula, what you tried and a brief description of your search efforts as is suggested in How to Ask.
    – Rubén - Volunteer Moderator - ♦
    Commented Jan 25, 2022 at 21:10
  • 2
    Your goal is not clear. Keep in mind that we can't see the spreadsheet you are looking at. We don't know what data you have, the data types, the layout or what your end goal is. The most effective and efficient way to get helps is to show us on an actual spreadsheet. Please share a link to the spreadsheet (or to a copy of it).
    – Erik Tyler
    Commented Jan 26, 2022 at 12:48
  • Hi and welcome to WebApps. but I don't want to have to add the formula to each new row that comes in It's generally agreed that one should not tamper with any sheet that is updated with Form responses. The alternative is to create a second sheet (a new sheet in the same spreadsheet) where the data from the responses is automatically imported (via query or filter, perhaps) and to add any formula on that sheet. @ErikTyler is right about our need to see your actual data but you might be able to use an arrayformula that will update the entire sheet.
    – Tedinoz
    Commented Jan 27, 2022 at 0:10
  • ok sure so here's a link to a test sheet: link: docs.google.com/spreadsheets/d/… if you look at row "8" you will see its missing the formula applied to it to check if its duplicated content, under the column "Duplicate Entries"
    – Shak
    Commented Jan 27, 2022 at 7:54
  • Okey doke. May I clarify something, please correct me if I am wrong. You began by discussing a formula (no details provided). But it seems that the actual purpose of the formula (the one on Column ) is to enable you to identify whether any of the form responses are exact duplicates. Is that right?
    – Tedinoz
    Commented Jan 31, 2022 at 23:04

1 Answer 1

Reset to default
0

When a sheet is a source of input (such as Form Responses), it is always best to avoid adding any extra fields to the sheet. Instead, in your scenario, create a second sheet that extracts the data from the original and applies your formula.

  • create a new sheet. the name doesn't matter

  • insert the following query in cell A1 of the new sheet.

=query(Sheet1!A1:K,"select * where A is not null")

This will automatically expand as new responses are received.

  • insert the following query in cell L1 of the new sheet.

={"Duplicate";ARRAYFORMULA(IF(len(A2:A100),if(COUNTIFS(C2:C&D2:D&E2:E&F2:F&G2:G&H2:H&I2:I&J2:J&K2:K,C2:C&D2:D&E2:E&F2:F&G2:G&H2:H&I2:I&J2:J&K2:K,ROW(A2:A),"<="&ROW(A2:A))=1,"OK","Duplicate"),iferror(1/0)))}

This formula will automatically expand to every row.

Sample Output

output

Sample data

sample data

Improve this answer
1
  • Thanks a million, I have applied this and it's working great have a great day!
    – Shak
    Commented Feb 1, 2022 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.