Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Convert excel formula into Javascript

New Here ,
May 15, 2020 May 15, 2020

Hello everyone,

I have excel spreadsheet that I'd like to convert into a fillable PDF but I don't know how to write in Javascript, can someone please help me ?

Here is what I do in Excel :

 - There is three variables Var A, Var B and Var B (picture 1)

Capture1.PNGexpand image

 - Depending of theses variables there is a first calculation (picture 2)

Capture2.PNGexpand image

 - And to finish a last calculation that show the result in one cell (picture 3)

Capture3.PNGexpand image

For information "SI" means "IF" and "ET" means "AND"

Thank you very much for your help 

TOPICS
Acrobat SDK and JavaScript
6.8K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 15, 2020 May 15, 2020

This is very basic JS syntax. "IF" in JS is "if" and "AND" in JS is "&&".

You can learn more about it in multiple locations, such as here: https://www.w3schools.com/js/

 

Note that while the basic syntax is the same, though, Acrobat and web JavaScript implementations have a different set of objects, properties and methods.

 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 15, 2020 May 15, 2020

I would suggest you consider learning JavaScript, or hiring an expert to help you with this conversion.

https://www.pdfscripting.com/public/main.cfm

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 16, 2020 May 16, 2020

First, you need to assign the values of all three fields to variables:

 

 

var a = this.getField("name of field A").value
var b = this.getField("name of field B").value
var c = this.getField("name of field C").value

 

 

Then for the calculation, I noticed that your first formula only considers var b to be equal to 2  and var c to be <= 35so:

 

 

if ((b == 2)&&(c <= 35)){
        //insert code here
}

 

 

you will nest the next if inside the first one:

 

 

if ((b == 2)&&(c <= 35)){
      if ((a >= 0)&&(a < 1339)) event.value = "Profil D"
      else if ((a >= 1340)&&(a < 1674)) event.value = "Profil C"
      else if ((a >= 1675)&&(a < 2344)) event.value = "Profil B"
      else if ((a >= 2345)&&(a < 3684)) event.value = "Profil A"
      else if (a >= 3685) event.value = "Profil A+"
      else event.value = ""
}
else event.value = ""

 

 

So the full code (assuming only integer will show in your fields) in the calculate script of your answer field (D9 in your example):

 

 

//Assign variables
var a = this.getField("name of field A").value
var b = this.getField("name of field B").value
var c = this.getField("name of field C").value

//Calculation
if ((b == 2)&&(c <= 35)){
      if ((a >= 0)&&(a < 1339)) event.value = "Profil D"
      else if ((a >= 1340)&&(a < 1674)) event.value = "Profil C"
      else if ((a >= 1675)&&(a < 2344)) event.value = "Profil B"
      else if ((a >= 2345)&&(a < 3684)) event.value = "Profil A"
      else if (a >= 3685) event.value = "Profil A+"
      else event.value = ""
}
else event.value = ""

 

 

 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 16, 2020 May 16, 2020

Remove all instances of "var" from the code, except for the first ones.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 16, 2020 May 16, 2020

that's a rookie mistake, lol, i'll correct this

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 01, 2021 Sep 01, 2021

Your very detailed! Would you mind offering some advice on custom scripts? They are relatively small but I want to make sure I have a good understanding.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 02, 2021 Sep 02, 2021
LATEST

Hi,

 

Just a small change, but ass there is a route where it will return event.value = ""; , that could just be set before hand and then if nothing happens it will be correct so the code can become

 

//Calculation
event.value = ""
if ((b == 2)&&(c <= 35)){
      if ((a >= 0)&&(a < 1339)) event.value = "Profil D"
      else if ((a >= 1340)&&(a < 1674)) event.value = "Profil C"
      else if ((a >= 1675)&&(a < 2344)) event.value = "Profil B"
      else if ((a >= 2345)&&(a < 3684)) event.value = "Profil A"
      else if (a >= 3685) event.value = "Profil A+"
}

 

I just find that a bit neater.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines