Copying data over to get rid of specific formulas

Question

I would like to use the same concept as is done in Copy value and format from a sheet to a new google Spreadsheet document? but only get rid of specific formulas but keep others. I want to create a new sheet with some of the formula values locked in but other still functioning. is this possible? This is what I tried so far. the get range on the last section is the only part that is erroring. I have also tried setting the numRow to sValues.length and the numColumn to s.Values.length[0] and still got an error.

function myFunction() {
   var ss = SpreadsheetApp.getActiveSheet();
   var sss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceName = ss.getSheetName();
  var sValues = ss.getRange(1,8,50,10).getValues();
  var ssValues = ss.getRange(1,91,14,11).getValues();
  var destinationSheet = sss.getSheetByName("New");
  ss.copyTo(sss).setName("New");
  destinationSheet.getRange(1,8,50,10).setValues(sValues);
  destinationSheet.getRange(1,91,14,11).setValues(sValues);
}

Trying to take this concept a bit further I ran into a weird issue that I cannot explain. I put the script below. It works perfectly at first but then the execution continues to load and once it finishes it takes the value7, value8, value9, value10 and value11 out of the new sheet that was created by copying it. so they show up at first but then disappear upon completion of the script. I have tried separating into different lines and separating these into different functions all together to see if these was a problem with the length of this script. running them on a different sheet all together to see if there was a glitch somewhere. The results were the same so I believe there is something about the value7-11 script. Does anyone know a solution or what is wrong?

function newSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
const  templateSheet = ss.getSheetByName("Template");
var sheetName = templateSheet.getRange("G1").getValue();
templateSheet.copyTo(ss).setName("F"+sheetName);
var ods = ss.getSheetByName("F"+sheetName);
var ds = SpreadsheetApp.openById("1meEEfjXuOKQ3jCZi2wwmvz7CH83crsPzgnhOEC0rDhI");
var sheet = ds.getSheets(); 
for (var i=0;i<sheet.length;i++){
  var sheets = sheet[i];
  var range = sheets.getRange("A1").getValue();
  var label = templateSheet.getRange("A1").getValue();
  var values1 = sheets.getRange("A9:B14").getValues();
  var values2 = sheets.getRange("G8").getValues();
  var values3 = sheets.getRange("A17:A22").getValues();
  var values4 = sheets.getRange("A25:A32").getValues();
  var values5 = sheets.getRange("A34:G44").getValues();
  var values6 = sheets.getRange("A45:A49").getValues();
  var values7 = sheets.getRange("A52:A58").getValues();
  var values8 = sheets.getRange("A62:A67").getValues();
  var values9 = sheets.getRange("E69:E74").getValues();
  var values10 = sheets.getRange("E100:E103").getValues();
  var values11 = sheets.getRange("H100:H101").getValues();
  if(range==label){
    ods.getRange("A9:B14").setValues(values1);
    ods.getRange("G8").setValues(values2);
    ods.getRange("A17:A22").setValues(values3);
    ods.getRange("A25:A32").setValues(values4);
    ods.getRange("A34:G44").setValues(values5);
    ods.getRange("A45:A49").setValues(values6);
    ods.getRange("A52:A58").setValues(values7);
    ods.getRange("A62:A67").setValues(values8);
    ods.getRange("E69:E74").setValues(values9);
    ods.getRange("E100:E103").setValues(values10);
    ods.getRange("H100:H101").setValues(values11);
  }
}
}

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jan 04 '23 at 00:49
For some clarity, I want to overwrite the formulas in the ranges (1,8,50,10) and (1,91,14,11) with just the values and not the formulas after the rest of the information on the sheet is copied over with the copyTo() function that is in this script. Every piece of this scrip does what I want it to do except the last two pieces that are written to bring over those values to overwrite the formulas. — Paperless, Jan 04 '23 at 19:25

Cooper · Answer 1 · 2023-01-16T23:25:31.067

0

function myFunction() {
   var ss = SpreadsheetApp.getActiveSheet();
   var sss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceName = ss.getSheetName();
  var sValues = ss.getRange(1,8,50,10).getValues();
  var ssValues = ss.getRange(1,91,14,11).getValues();
  var destinationSheet = ss.copyTo(sss).setName("New");
  destinationSheet.getRange(1,8,50,10).setValues(sValues);
  destinationSheet.getRange(1,91,14,11).setValues(ssValues);
}

edited Jan 16 '23 at 23:25

answered Jan 04 '23 at 00:10

Cooper

59,616
6
23
54

When I run this script it says TypeError: Cannot read properties of null (reading 'getRange'). Is there some syntax error I am missing? – Paperless Jan 16 '23 at 18:48
What line is failing? – Cooper Jan 16 '23 at 21:50
destinationSheet.getRange(1,8,50,10).setValues(sValues); – Paperless Jan 16 '23 at 22:04
But I would assume destinationSheet.getRange(1,91,14,11).setValues(ssValues); would have the same problem it just didn't read that far. – Paperless Jan 16 '23 at 22:05
Do you have a destinationSheet named "New"? – Cooper Jan 16 '23 at 23:22
Yes when I run the script it makes the copy of the original sheet and sets the name of the "New". I played around with it a little and think that the problem was to do with the way I inputted the range. I switched to A1 notation instead and that fixed the issue. Thanks for the help! – Paperless Jan 17 '23 at 17:09

Copying data over to get rid of specific formulas

1 Answers1