Just today, the function stopped working - insert the result of calculating the formula as a value. (In Google Apps Scripts, Google Spreadsheets). At present, this function works as clearContents () - it simply overwrites all values.

Example:

function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getActiveSheet() var range = sheet.getRange("A1:C3") range.setFormula("=1+2") range.copyTo(range, {contentsOnly:true}) //Или так: //range.copyValuesToRange(sheet, range.getColumn(), range.getLastColumn(), range.getRow(), range.getRowIndex()) //range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false) }

You can use other methods, conjuring with formats, but why did it stop working ??

 range.setValue(range.getValues()) range.setValue(range.getDisplayValues())

    1 answer 1

    Suddenly, it will be interesting to anyone despite the negative question:

    You must add before range.copyTo(range, {contentsOnly:true}) SpreadsheetApp.flush()

    According to the documentation, this method applies all pending changes to the table.

    Applies all pending Spreadsheet changes.

    That is, as I understand it, earlier, when I called copyTo, google, before I took the data from the cells, I applied all the changes myself (I calculated the results of the formulas), and after that I already did the copying. Now you need to set this moment manually.

    UPD1: According to the oshliaer comment, you can also use the Sheets API.

    As far as I understand, the code will be somewhere like this:

     // ss = SpreadsheetApp.getActiveSpreadsheet() // sheet = ss.getSheetByName("NAME") //Для примера: range = sheet.getRange("A1:C") function killAllFormulas2(ss, sheet, range) { // Обязателен вызов flush(), иначе если вы только вставили сложные формулы и сразу захотели вставить результаты вычисления как значения - они могут не успеть обновить данные в ячейках SpreadsheetApp.flush() // Получаем ID вашего Spreadsheet var ssID = ss.getId() // Если выбран такой диапазон как в примере - его обязательно нужно перевести в вид A1:C1000 (условно) var rangeA1 = sheet.getRange(range.getRow(), range.getColumn(), range.getLastRow()-range.getRow()+1, range.getLastColumn()-range.getColumn()+1).getA1Notation() // Получаем значения var values = Sheets.Spreadsheets.Values.get(ssID, sheet.getName() + "!" + rangeA1) // Вставляем значения Sheets.Spreadsheets.Values.update(values, ssID, sheet.getName() + "!" + rangeA1, {valueInputOption:"USER_ENTERED"}) }

    In the examples that are found here it is stated that for most use cases it is better to use the standard methods of the built-in SpreadsheetApp (for example, a quote to write data:

    SpreadsheetApp.getActiveSpreadsheet (). GetRange (range) .getValues ​​(values) is more appropriate.

    I checked the speed of work on some ranges with complex formulas - I did not see the difference at all. Most likely this is all the same algorithm.

    I hope this answer will help someone not to waste precious time looking for the truth.

    • one
      Unfortunately not. flush has always been required, but its usefulness is apparently questionable. copyTo , as I understand it, is not the best sugar over copyTo API. If you do the same through the Advanced Services Sheet API, this does not happen. I recommend using copyTo only in cases where it is truly justified. - oshliaer
    • one
      I agree with the conclusions. My comment applies only to copyTo . getValues ​​<=> setValues ​​should work better. - oshliaer
    • one
      Thank you so much for helping me figure it out! - Sergey Pryanichkin
    • one
      Added example copyTo github.com/oshliaer/google-apps-script-snippets#copyto - oshliaer
    • one
      But more than a year I worked at 100% normal copyTo with contentsOnly: true. Never let down and the speed is excellent. It was necessary to get in there ... Probably one thing was fixed, the second one was under-fixed) - Sergey Pryanichkin