'Concate String to a formula in app script

I have a values in google sheet and the format is =+40,-58. This give me ERROR! because the sheet is taking it as formula. I can manually edit this by adding ' single qoute before equal sign but when i append qoute using script it append qoute with ERROR!. Tried multiple thing like getting cell type, convert it to string. Tried set formula method but it appends another equal sign before the cell value

please check the code below

if (//my condition){
      
 sheet.getRange(i,col_in+1).setValue("'"+colvalue)

I am looking for possible solutions like, how can I get the actual value of the cell from fx or How can i append a single quote with the cell value instead of appending quote with ERROR.

please see the screenshot of the sheet

Solution 1:^[1]

Descrition

Because the formula is giving "#ERROR" you need to getFormula and use setValue

Script

function test() {
  let cell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A6");
  let value = cell.getFormula();
  if( value !== "" ) {
    console.log("formula = "+value);
    cell.setValue("'"+value);
  }
}

Console.log

7:30:31 AM  Notice  Execution started
7:30:31 AM  Info    formula = =+52,-64
7:30:32 AM  Notice  Execution completed

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	TheWizEd

'Concate String to a formula in app script

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]