CODEWITHSUNDEEP
google-apps-script
Marc L
Marc L

Reputation: 887

setValues not writing in Google script

So in the code below the function seems to run ok until it comes to clear the sheet and overwrite it with just the values (I don't want the formulaes in the second sheet). Unfortunately for some reason I end up with a blank sheet and the values are not being written. Anyone know what the reason could be? Thanks

    function newMonthFunction() {

  // what files we want
 var FindThisText = "Core Live 2016";
 var files = DriveApp.searchFiles('title contains "' + FindThisText + '" and mimeType = "application/vnd.google-apps.spreadsheet"')



 // make a copy for the new month 
  while (files.hasNext()) {
    var file = files.next();
    var sheet = SpreadsheetApp.getActiveSheet()
    var range = sheet.getRange(2,2); 
    var NewMonth = range.getValue();
    var editors = file.getEditors();
    var viewers = file.getViewers();
    var file = DriveApp.getFileById(file.getId());
    var FileRenameStart =file.getName();
    var FileRenameend = "Raizcorp Core "  + NewMonth;
    DriveApp.getFileById(file.getId()).makeCopy(FileRenameend);



    // Get Workbooks
    var filesNew = DriveApp.searchFiles('title contains "' + FileRenameend + '" and mimeType = "application/vnd.google-apps.spreadsheet"');
    var fileNew = filesNew.next();

    // Set security for new sheet
    for (var i = 0; i<viewers.length;i++) {
        fileNew.addViewer(viewers[i])
      };
    for (var i = 0; i<editors.length;i++) {
       fileNew.addEditor(editors[i])
      };


    var source = SpreadsheetApp.openById(file.getId());
    var target = SpreadsheetApp.openById(fileNew.getId());

    //Get Spreadsheets
    var source_sheet = source.getSheetByName("Entrepreneur List");
    var target_sheet = target.getSheetByName("Entrepreneur List");

    // Get target last row
    var last_row = target_sheet.getLastRow();

    // Set Ranges
    var source_range = source_sheet.getRange("A:AU");
    var target_range = target_sheet.getRange("A:AU");
    target_range.clear()

    // Fetch values
    var MyValues = source_range.getValues();

    // Save to spreadsheet
   target_range.setValues(MyValues);
  }}

Upvotes: 0

Views: 147

Answers (1)

Tiffany G. Wilson
Tiffany G. Wilson

Reputation: 573

If your source and target sheets have a different number of rows, the write command may not work due to the different sizes of the A:AU arrays. To ensure the target range is the right size, use MyValues to set it.

Try

target_range = target_sheet.getRange("A1").offset(0,0,MyValues.length,MyValues[0].length); target_range.setValues(MyValues);

Upvotes: 1

Related Questions