Losing importXML functions when running my current

问题:

I am writing a Google script to copy values from a column of importXML data based on a certain date. Below is where I am currently, but the issue I am running into now is when the script is run it is removing the importXML formulas located in columnns G and H:

function moveValuesOnly2() {
var sheet = SpreadsheetApp.openById("0At2fAZApHI8adHJxWU5EVVBEbHd0YzA5UVBwOGQ3ZHc");
SpreadsheetApp.setActiveSpreadsheet(sheet);
SpreadsheetApp.setActiveSheet(sheet.getSheetByName('Dylan'))

var range= sheet.getRange("B3:AJ50");
var currentValue = range.getValues();
var COL_B = 0;  // relative to col B
var COL_G = 5;  
var COL_H = 6;
var COL_I = 7;
var COL_J = 8;
var COL_K = 9;
var COL_L = 10;  
var COL_N = 12;
var COL_O = 13;
var COL_P = 14;     
var COL_R = 16; 
var COL_S = 17;     
var COL_T = 18;
var COL_V = 20;
var COL_W = 21;
var COL_X = 22;
var COL_Z = 24;
var COL_AA = 25;
var COL_AB = 26;
var COL_AD = 28;
var COL_AE = 29;
var COL_AF = 30;
var COL_AH = 32;
var COL_AI = 33;
var COL_AJ = 34;  

for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (1)) {

// Copy value at 0 days
currentValue[i][COL_J] = currentValue[i][COL_G];
currentValue[i][COL_K] = currentValue[i][COL_H];
currentValue[i][COL_L] = currentValue[i][COL_I];
}

for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (15)) {

// Copy value at 15 days 
currentValue[i][COL_N] = currentValue[i][COL_G];
currentValue[i][COL_O] = currentValue[i][COL_H];
currentValue[i][COL_P] = currentValue[i][COL_I];  
}
range.setValues(currentValue);
}  
for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (30)) {

// Copy value at 30 days 
currentValue[i][COL_R] = currentValue[i][COL_G];
currentValue[i][COL_S] = currentValue[i][COL_H];
currentValue[i][COL_T] = currentValue[i][COL_I];
}
range.setValues(currentValue);
}  
for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (45)) {

// Copy value at 45 days
currentValue[i][COL_V] = currentValue[i][COL_G];
currentValue[i][COL_W] = currentValue[i][COL_H];
currentValue[i][COL_X] = currentValue[i][COL_I];
}
range.setValues(currentValue);
}
for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (60)) {

// Copy value at 60 days
currentValue[i][COL_Z] = currentValue[i][COL_G];
currentValue[i][COL_AA] = currentValue[i][COL_H];
currentValue[i][COL_AB] = currentValue[i][COL_I];
}
range.setValues(currentValue);  
}  
for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (75)) {

// Copy value at 75 days 
currentValue[i][COL_AD] = currentValue[i][COL_G];
currentValue[i][COL_AE] = currentValue[i][COL_H];
currentValue[i][COL_AF] = currentValue[i][COL_I];
}
range.setValues(currentValue);
}
for(var i = 0 ; i < currentValue.length ; i++){
if (currentValue[i][0] == (90)) {

// Copy value at 90 days
currentValue[i][COL_AH] = currentValue[i][COL_G];
currentValue[i][COL_AI] = currentValue[i][COL_H];
currentValue[i][COL_AJ] = currentValue[i][COL_I];
}
range.setValues(currentValue);  
}}}

Is there any way to prevent the ImportXML functions located in those columns from being lost each time the script is run. Also a huge thank you to user @Srik to getting to me to the point I am at!

回答1:

I would think that the problem lies from the fact that you setValues on the range which includes your columns G & H.

The solution lies in using

var specialRange = sheet.getRange("G3:H50");
var formulas = specialRange.getFormulas();

at the begining of your script to save the formulas and then after your setValues call to use the following code

specialRange.setFormulas(formulas);

Let me know how that works for you.