Concate String to a formula in app script-CodePudding

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

CodePudding user response：

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