I am writing a Google App Script, and I've gotten stuck. I am a beginner.
I have a .csv from our SQL server with 943 rows that is uploaded to my google drive. This script takes the contents of the .csv and moves it to a Google Sheet for use on my website.
It is working as long as the number of rows in the csv doesn't change. As items are added or removed from our web store, the script will not work and throws the error:
"Exception: The number of rows in the data does not match the number of rows in the range. The data has 943 but the range has 944."
function CSVCopyPaste(sourcelink,sourcerange,destilink,destisheet,destirange {
//Source link
var file = DriveApp.getFilesByName('CommercialAvailability.csv').next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
// Destination
var ss = SpreadsheetApp.openByUrl(destilink);
var sheet = ss.getSheetByName(destisheet);
// transfer to destination range
sheet.getRange(destirange).clearContent();
sheet.getRange(destirange).setValues(csvData);
}
The second function is called CommercialAvailability and it is the function I'm actually running to accomplish the result. It is:
function CommercialAvailability() {
SettlemyreCSVCopyPaste(
"https://drive.google.com/file/d/1-V040x0t6SWT14xx6N22MlVFhHnj9XE4",
"A3:C",
"https://docs.google.com/spreadsheets/d/1s8kzVxmJ6v3akpoZ8N2VoGMZ90U2kozlSXdRHUU2BAg/edit#gid=0",
"Commercial Availability",
"B6:D945"
)
}
Any help with this would be greatly appreciated!!
Thank you,
Alex
Alex, the issue is that your range is fixed (B6:D945) but the size of the data in the CSV is variable.
Try:
function CSVCopyPaste(sourcelink,sourcerange,destilink,destisheet,destirangestart) {
//Source link
var file = DriveApp.getFilesByName('CommercialAvailability.csv').next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
//we need to check if the CSV is not empty
if (csvData.length > 0){
// Destination
var ss = SpreadsheetApp.openByUrl(destilink);
var sheet = ss.getSheetByName(destisheet);
var rangestart = sheet.getRange(destirangestart);
var writeRange = sheet.getRange(rangestart.getRow(),rangestart.getColumn(),csvData.length,csvData[0].length);
var clearRange = sheet.getRange(rangestart.getRow(),rangestart.getColumn(),sheet.getMaxRows()-rangestart.getRow(),csvData[0].length);
// transfer to destination range
clearRange.clearContent();
writeRange.setValues(csvData);
}
}
function CommercialAvailability() {
CSVCopyPaste(
"https://drive.google.com/file/d/1-V040x0t6SWT14xx6N22MlVFhHnj9XE4",
"A3:C",
"https://docs.google.com/spreadsheets/d/1s8kzVxmJ6v3akpoZ8N2VoGMZ90U2kozlSXdRHUU2BAg/edit#gid=0",
"Commercial Availability",
"B6"
)
}
Note that I haven't tested it. Also, you have unused arguments in CSVCopyPaste that you may want to get rid of.