get cells in range and paste as values Apps script-CodePudding

I have a range A2:Z5000 that i am trying to paste as values in the same exact range (turn formulas into fixed values)

In range A2 i have a ImportData formula fetching data from a csv file.

My script goes like:

var sheet = SpreadsheetApp.getActive().getSheetByName('MYSQLimport');
sheet.getRange("A2:Z5000").copyTo(sheet.getRange("A2:Z5000"), {contentsOnly:true});

but it erases the content instead of setting it as values...

CodePudding user response：

I ran your code on a sample sheet and it works for me as well.

Sample sheet: https://docs.google.com/spreadsheets/d/1IrEG_YNoUnesg78CDjlEwsVqssElZZ8znciqJC63D1Y/edit#gid=1190912023

CodePudding user response：

Description

copyTo copies the contents being values and/or formulas you need to gettValues()/setValues().

Script

var values = sheet.getRange("A2:Z5000").getValues();
sheet.getRange("A2:Z5000").setValues(values);