Bad Value Error Google Apps Script

Question

I am getting an error stating "Bad Value at Line 35" after every run of my code. The code actually runs through all the way once, but then there is a second file that is created named "undefined" and seems to be part of this error. Nowhere in my code do I have the script create multiple spreadsheets, we are only having it create one. It would be greatly appreciated if I could find a solution to this issue. Code Below:

function ssCreation(spreadsheetName, spreadsheetId1, sourceSheetName1,
    spreadsheetId2, sourceSheetName2,
    spreadsheetId3, sourceSheetName3,
    spreadsheetId4, sourceSheetName4,
    spreadsheetId5, sourceSheetName5,
    spreadsheetId6, sourceSheetName6,
    spreadsheetId7, sourceSheetName7,
    spreadsheetId8, sourceSheetName8,
    spreadsheetId9, sourceSheetName9,
    spreadsheetId10, sourceSheetName10,
    spreadsheetId11, sourceSheetName11) {

    var create = SpreadsheetApp.create(spreadsheetName).getId();
    var renameSheet = SpreadsheetApp.openById(create).renameActiveSheet('1');
    for (var i = 1; i < 14; i++) {
        var sheetName = 1 + i;
        var insertTheSheet = SpreadsheetApp.openById(create).insertSheet(i);
    }

    var ss1 = SpreadsheetApp.openById(spreadsheetId1);
    var ssd1 = SpreadsheetApp.openById(create);
    var sourceSheet1 = ss1.getSheetByName(sourceSheetName1);
    var sourceData1 = sourceSheet1.getDataRange().getValues();
    var destSheet1 = ssd1.getSheetByName("1");
    destSheet1.getRange(destSheet1.getLastRow() + 1, 1, sourceData1.length, sourceData1[0].length).setValues(sourceData1);

    var ss2 = SpreadsheetApp.openById(spreadsheetId2);
    var ssd2 = SpreadsheetApp.openById(create);
    var sourceSheet2 = ss2.getSheetByName(sourceSheetName2);
    var sourceData2 = sourceSheet2.getDataRange().getValues();
    var destSheet2 = ssd2.getSheetByName("Sheet2");
    destSheet2.getRange(destSheet2.getLastRow() + 1, 1, sourceData2.length, sourceData2[0].length).setValues(sourceData2);

    var ss3 = SpreadsheetApp.openById(spreadsheetId3);
    var ssd3 = SpreadsheetApp.openById(create);
    var sourceSheet3 = ss3.getSheetByName(sourceSheetName3);
    var sourceData3 = sourceSheet3.getDataRange().getValues();
    var destSheet3 = ssd3.getSheetByName("Sheet3");
    destSheet3.getRange(destSheet3.getLastRow() + 1, 1, sourceData3.length, sourceData3[0].length).setValues(sourceData3);

    var ss4 = SpreadsheetApp.openById(spreadsheetId4);
    var ssd4 = SpreadsheetApp.openById(create);
    var sourceSheet4 = ss4.getSheetByName(sourceSheetName4);
    var sourceData4 = sourceSheet4.getDataRange().getValues();
    var destSheet4 = ssd4.getSheetByName("Sheet4");
    destSheet4.getRange(destSheet4.getLastRow() + 1, 1, sourceData4.length, sourceData4[0].length).setValues(sourceData4);

    var ss5 = SpreadsheetApp.openById(spreadsheetId5);
    var ssd5 = SpreadsheetApp.openById(create);
    var sourceSheet5 = ss5.getSheetByName(sourceSheetName5);
    var sourceData5 = sourceSheet5.getDataRange().getValues();
    var destSheet5 = ssd5.getSheetByName("Sheet5");
    destSheet5.getRange(destSheet5.getLastRow() + 1, 1, sourceData5.length, sourceData5[0].length).setValues(sourceData5);

    var ss6 = SpreadsheetApp.openById(spreadsheetId6);
    var ssd6 = SpreadsheetApp.openById(create);
    var sourceSheet6 = ss6.getSheetByName(sourceSheetName6);
    var sourceData6 = sourceSheet6.getDataRange().getValues();
    var destSheet6 = ssd6.getSheetByName("Sheet6");
    destSheet6.getRange(destSheet6.getLastRow() + 1, 1, sourceData6.length, sourceData6[0].length).setValues(sourceData6);

    var ss7 = SpreadsheetApp.openById(spreadsheetId7);
    var ssd7 = SpreadsheetApp.openById(create);
    var sourceSheet7 = ss7.getSheetByName(sourceSheetName7);
    var sourceData7 = sourceSheet7.getDataRange().getValues();
    var destSheet7 = ssd7.getSheetByName("Sheet7");
    destSheet7.getRange(destSheet7.getLastRow() + 1, 1, sourceData7.length, sourceData7[0].length).setValues(sourceData7);

    var ss8 = SpreadsheetApp.openById(spreadsheetId8);
    var ssd8 = SpreadsheetApp.openById(create);
    var sourceSheet8 = ss8.getSheetByName(sourceSheetName8);
    var sourceData8 = sourceSheet8.getDataRange().getValues();
    var destSheet8 = ssd8.getSheetByName("Sheet8");
    destSheet8.getRange(destSheet8.getLastRow() + 1, 1, sourceData8.length, sourceData8[0].length).setValues(sourceData8);

    var ss9 = SpreadsheetApp.openById(spreadsheetId9);
    var ssd9 = SpreadsheetApp.openById(create);
    var sourceSheet9 = ss9.getSheetByName(sourceSheetName9);
    var sourceData9 = sourceSheet9.getDataRange().getValues();
    var destSheet9 = ssd9.getSheetByName("Sheet9");
    destSheet9.getRange(destSheet9.getLastRow() + 1, 1, sourceData9.length, sourceData9[0].length).setValues(sourceData9);

    var ss10 = SpreadsheetApp.openById(spreadsheetId10);
    var ssd10 = SpreadsheetApp.openById(create);
    var sourceSheet10 = ss10.getSheetByName(sourceSheetName10);
    var sourceData10 = sourceSheet10.getDataRange().getValues();
    var destSheet10 = ssd10.getSheetByName("Sheet10");
    destSheet10.getRange(destSheet10.getLastRow() + 1, 1, sourceData10.length, sourceData10[0].length).setValues(sourceData10);

    var ss11 = SpreadsheetApp.openById(spreadsheetId11);
    var ssd11 = SpreadsheetApp.openById(create);
    var sourceSheet11 = ss11.getSheetByName(sourceSheetName11);
    var sourceData11 = sourceSheet11.getDataRange().getValues();
    var destSheet11 = ssd11.getSheetByName("Sheet11");
    destSheet11.getRange(destSheet11.getLastRow() + 1, 1, sourceData11.length, sourceData11[0].length).setValues(sourceData11);

}

Your file named "undefined" is the Spreadsheet object you've assigned to create -- you have created a file, but not given it a name. You need to rethink your approach here: you have the same code copied 10 times & a multiple variables referring to the same object. I'll flesh out a better way of organising the code in an answer. — Dean Ransevycz
– Dean Ransevycz, Commented Sep 15, 2017 at 2:42
Sorry, misread the code. forget the comment about not assigning a name — Dean Ransevycz
– Dean Ransevycz, Commented Sep 15, 2017 at 2:52

Dean Ransevycz · Accepted Answer · 2017-09-15 03:55:30Z

Rather than pass 23 parameters in to a function to create a new spreadsheet, then copy data from 11 other spreadsheets, why not farm out the copy another function & then pass the source sheet info to that function? That way you only need to debug on section of code for every copy operation, rather than the 11 duplicates that you have in your current version. Below i've put together a way of doing this: your repeated 6 lines of data copy code is now 5 lines in the copyData() function.

We create the destination spreadsheet & refer to it only once as we pass the Spreadsheet object to copyData(). The array sheetsList[] is a sort of key=>value pair list. So to copy from source to destination we call copyData() once for each key=>value pair in sheetsList[]. We pass the "spreadsheetIdN" (key), "sourceSheetNameN' (value) to copyData() along with the destinationSpreadsheet object & the name of the sheet in the destination spreadsheet that will accept the data from the source.

Hope this helps.

function copyData(sourceSpreadsheetID, sourceSheetName, destSpreadsheet, destSheetName){
    // sourceSpreadsheetID - ID of the source spreadsheet (string)
    // sourceSheetName - name of the sheet in the source spreadsheet (string)
    // destSpreadsheet - the destination spreadsheet object
    // destSheetName - name of the destination sheet (string)
    var srcSS = SpreadsheetApp.openById(sourceSpreadsheetID);
    var sourceSheet = srcSS.getSheetByName(sourceSheetName);
    var sourceData = sourceSheet.getDataRange().getValues();
    var destSheet = destSpreadsheet.getSheetByName(destSheetName);
    destSheet.getRange(destSheet.getLastRow() + 1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);
}

function doDataCopy(){
  // create the destination spreadsheet & store it as a Spreadsheet object that we can pass to copyData()
  var destinationSpreadsheet = SpreadsheetApp.create("a spreadsheet");
  // the source spreadsheets & sheets as sort of (key, value) pairs
  var sheetsList = ["spreadsheetId1", "sourceSheetName1",
                    "spreadsheetId2", "sourceSheetName2",
                    "spreadsheetId3", "sourceSheetName3",
                    "spreadsheetId4", "sourceSheetName4",
                    "spreadsheetId5", "sourceSheetName5",
                    "spreadsheetId6", "sourceSheetName6",
                    "spreadsheetId7", "sourceSheetName7",
                    "spreadsheetId8", "sourceSheetName8",
                    "spreadsheetId9", "sourceSheetName9",
                    "spreadsheetId10", "sourceSheetName10",
                    "spreadsheetId11", "sourceSheetName11"
                    ];
  for(var i = 0, lim = (sheetsList.length / 2); i < lim; ++i){
    destinationSpreadsheet.insertSheet("Sheet" + (i+2), i+1); // <= we have to skip the name "Sheet1" as the newly created sheet already has a "Sheet1"
    // As we're treating sheetsList[] as (key,value) pairs, the index of each 'key' is i*2 & each 'value' is (i*2)+1
    // Also we can pass the destinationSpreadsheet object as is & copyData() can work on it directly
    copyData(sheetsList[i*2], sheetsList[(i*2)+1], destinationSpreadsheet, "Sheet" + (i+1)); 
  } 
}

Collectives™ on Stack Overflow

Bad Value Error Google Apps Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related