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.

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
    Jan 25 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
    Jan 26 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
    Jan 27 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
    Jan 27 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
    Jan 31 at 23:04

1 Answer 1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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