SetFormula

[Silvio Cesar Dos Santos Junior]

Hi guys, what's up?

I am having problems using the function setFormula, my script adds the formulas in the right places and finds the value but it doesn't insert it in the cell and gives the error "Formula analysis error", can you guys help me? 

The REGEXREPLACE function to remove accents is working normally, but I'm not getting the PROCV (VLOOKUP) function to work as it should. I will also leave some photos of the errors if it helps.

The range with the formulas that are not working:

Proving that he finds the right value:

But it always returns this error:

This is my formula function:

function formulas(ownboardingDate){

  

  var targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 

  var targetPage = targetSpreadsheet.getSheetByName("05/10");

  

  var name = targetPage.getRange("D2:D300");

  name.setFormula('=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(C2;"[ùúüû]";"u");"[èéêë]";"e");"[àáãâäå]";"a");"[íìîï]";"i");"[óòöôõ]";"o");"ç";"c");"[ÙÚÜÛ]";"U");"[ÈÉÊË]";"E");"[ÀÁÃÂÄÅ]";"A");"[ÍÌÎÏ]";"I");"[ÓÒÖÔÕ]";"O");"Ç";"C")');

  

  var user = targetPage.getRange("E2:E30");

  user.setFormula('=PROCV(D2; Users!D2:G1840; 2; FALSO)');

  var email = targetPage.getRange("F2");

  email.setFormula('=PROCV(D2; Users!D2:G1840; 3;FALSO())');

  var passWord = targetPage.getRange("G2");

  passWord.setFormula('=PROCV(D2; Users!D2:G1840; 4;FALSO())');

  

  //style(ownboardingDate);

  

}

[Silvio Cesar Dos Santos Junior]

[Jean-Luc Vanhulst]

You should try to get the formula to work in a regular cell first? Right now if I paste the formula in a cell I get N/A.

Also do you need to save the original values? Otherwise you might as well replace the value instead of using a monster formula?

On top of that you'll have to make sure to use setFormulaR1C1() and use a relative value for 'C2', since I assume you want every cell in the D column to be updated and not that every cell in the D column will be individually updated?

If you simply want to update the cell values consider this code:

function strip(string) {return string.normalize("NFD").replace(/[\u0300-\u036f]/g, "") }

  var name = targetPage.getRange("D2:D300");

  var array = name.getValues();

  array.forEach( (e,indx) => array[indx][0] = strip( e[0]) ); 

  name.setValues(array);

  or if you want a formulized version:

  var name = targetPage.getRange("D2:D300");

  name.setFormula("=strip(r[-1]c[0])") // or replace strip() with a correct, working formula and use r[0]c[0] where you have C2 now.  (assuming that you want D2 to have the stripped version of C2 and D3 the stripped version of C3 etc. 

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/82840ec4-57b2-45fc-909a-91a8ed3690f2n%40googlegroups.com.

[Silvio Cesar Dos Santos Junior]

Yes that is exactly what i want, the formulas on E2, F2 and G2 look for the D2 value with the vlookup (procv) and the E3, F3 and G3 look for the D3 value.
I already tried to use R1C1 like you said and i had the same error.

[Jean-Luc Vanhulst]

You have to be a little bit more specific on 'the same error'

The example formula you have doesn't work. You need to have a working formula to begin with (in a normal cell.). Then you can copy the formula and use it in your script. 

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/88ef72c9-c285-4ff7-8108-a5059e032195n%40googlegroups.com.

[Silvio Cesar Dos Santos Junior]

When i insert the exactly same formula manually on Sheets works fine, but when i use it with the setFormula method in script i have the error "Formula analysis error." even if I try to use setFormula any of these ways:

The formula: =PROCV(D2; Users!D3:G1841; 2; FALSO())

This one gives me a different error, Unknown function: "PROCV". (PROCV is VLOOKUP in portuguese) 

var user = targetPage.getRange("E2:E11");

user.setFormula('=PROCV(D2; Users!D2:G1840; 2; FALSO())') ;

This way i have the error Formula analysis error.

var user = targetPage.getRange("E2:E11");

user.setFormulaR1C1('=PROCV(R[0]C[-1]; Users!D2:G1840; 2)') ;

This way i have the error Formula analysis error.

var user = targetPage.getRange("E2:E11");

user.setFormulaR1C1('=PROCV(R[0]C[-1]; Users!D2:G1840; 2; FALSO())') ;

This way i have the error Formula analysis error.

var user = targetPage.getRange("E2:E11");

user.setFormula('=PROCV(R[0]C[-1]; Users!D2:G1840; 2; FALSO())') ;

This way i have the error Formula analysis error.

var user = targetPage.getRange("E2:E11");

user.setFormula('=PROCV(R[0]C[-1]; Users!D2:G1840; 2)') ;

Procv doc if you want to know more about:

https://support.google.com/docs/answer/3093318?hl=pt

"

[Jean-Luc Vanhulst]

From what I understand you will have to use VLOOKUP in the script. The front end will still show the local name.

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1c1c439c-1a95-4bd8-842b-2955ef31b486n%40googlegroups.com.

[MANNY Carzola]

Hello everyone, I'm trying to upload this and make it functional in google sheets. I know how to upload to sheets do it all the time but 

the functions are not working. Need help or help for hire on this one to make this a full functional google sheet. 

 PRMG Responder Profile Software W030-035.xls

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1c1c439c-1a95-4bd8-842b-2955ef31b486n%40googlegroups.com.

--

Manny Carzola 

Former NAVY IT SPECIALIST

Bachelor Information Systems Computer Forensics

CISSP Bank Financial Security Specialist

Certified White Hat Hacker

IT HIPAA Board --- PCI Compliance

Intrusion Detection\Forensics Gov

[Jean-Luc Vanhulst]

You;ll have to start by making it accessible to use to see! (Sharing settings)

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHWh5OFwj1kBX%2BBzj9_Tpn_E7mxQwJRsNBQ%3Df-6PfBkf2qC5MA%40mail.gmail.com.

[MANNY Carzola]

You mean my google sheet?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ52F5udf7SgT_U9516TZ%3D6hZ3r_UKCU9TdkrayJs_1OLCw%40mail.gmail.com.

[Jean-Luc Vanhulst]

Yes when I click I can't open it. You need to make it at least 'anyone with the link can view'

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHWh5OGZ0sW7Rfbd5dQ1j_QM7iHJrrRgCHwTC97OvuddEDMtcA%40mail.gmail.com.

[MANNY Carzola]

THe sharing option is not behaving now, maybe its down . Are you able to open the excel spreadsheet on your end and test it ? Google sheets, 

the issue is the submit buttons type of function , that the info goes on the 3rd tabs

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ51e6F0zoq81u_fQtUACQ6MBZ9tDm1buz5%2BfsA%3Dy65_YFg%40mail.gmail.com.

[Martin Molloy]

i don't think the word  FALSO()  will work in your formula

I think it should just be FALSO

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1c1c439c-1a95-4bd8-842b-2955ef31b486n%40googlegroups.com.

[Silvio Cesar Dos Santos Junior]

I made it work using:

var user = targetPage.getRange("E2:E1000");

 user.setFormula('=VLOOKUP(D2; Users!D2:G1840; 2; )') ;

Thanks for everyone who helped me.

[Silvio Cesar Dos Santos Junior]

The range is E2:E1000 because that way is more easy to delete the rows i will not use.