So basically I'm importing 2 columns of data with importrange, but I don't want the values to be updated in real time. Right now I have 2 menu buttons: 'Refresh' clears the data and re-imports the data, and 'Save' freezes the values by copying to the same cells.
I'm trying to combine both functions into one step so I can assign the script to a spreadsheet button for users to click for whenever they want to refresh the data. But when I try to combine both functions into one the range just clears without refreshing. Anybody knows what's the issue?
function refresh() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Summary')
sheet.getRange('C:D').clearContent()
var desCell = sheet.getRange("A4");
desCell.setFormula('=importrange("URL")');
var range = sheet.getRange('A:D')
range.copyTo(range, {contentsOnly: true});//
}
For some reason separating both functions like the following works but like I said I'll like to combine both functions so I can them assign to one button.
function refresh() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Summary')
sheet.getRange('C:D').clearContent()
var desCell = sheet.getRange("C2");
desCell.setFormula('=importrange("URL to source sheet")');
}
function freeze() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Summary')
var range = sheet.getRange('C:D')
range.copyTo(range, {contentsOnly: true});
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Update Numbers')
.addItem('Refresh', 'refresh')
.addItem('Save','freeze')
.addToUi()
}