0
98
ViewsConcate 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
1 answers
Answer question0
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