I learning how to use Google Script. I'm trying to make a copy each 50 rows of a sheet from a Spreadsheet file with 35,000 rows and that demand a lot manualy. Please, how to do that with Google Script?
In some topics, I tried the adapted code below and it worked for me, but I'm having troubles to get a array of 50 rows each.
function export50rows() {
var sheetName = "My sheet Name";
var folderID = "Folder ID to copy";
var destFolder = DriveApp.getFolderById(folderID);
var srcSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var data = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"dd-MM-yyyy hh_mm a");
var file = DriveApp.getFileById(srcSpreadSheet.getId()).makeCopy("My sheet Name " + data, destFolder);
var dstSpreadsheet = SpreadsheetApp.open(file);
dstSpreadsheet.getSheets().forEach(s => {
if (s.getSheetName() != sheetName) {
dstSpreadsheet.deleteSheet(s);
}
});
Browser.msgBox("WORKED!")
}
Copy file 50 rows at a time to a new file in a folder
I made some changes and I did not test it. It copies 50 rows into a new sheet insert into a new Spreadsheet in the destination. It increments the starting row for the data by 50 and stores that in property service.
So you can just keep running and on each execution it will continue to increment the start row of the data file to the next location,
function export50rows() {
const folder = DriveApp.getFolderById(gobj.globals.folder1id);//folderid
const tmpltFile = DriveApp.getFileById(gobj.globals.test2id);//templateid
const sss = SpreadsheetApp.openById(gobj.globals.test1id)//source fileid
const sh = sss.getSheetByName("Sheet0");//Sheet Name
const hs = sh.getRange(1,1,1,sh.getLastColumn()).getValues().flat();
var sr;
let start = PropertiesService.getScriptProperties().getProperty("start");
if(!start) {
PropertiesService.getScriptProperties().setProperty("start","2");
sr = 2;
} else {
sr = parseInt(start);
}
const vs = sh.getRange(sr,1,50,sh.getLastColumn()).getValues();
vs.unshift(hs);
const ds = Utilities.formatDate(new Date(),sss.getSpreadsheetTimeZone(),"MM/dd/yyyy HH:mm:ss");
const file = tmpltFile.makeCopy(`${sss.getName()}-${ds}`,folder);
const dss = SpreadsheetApp.open(file);
let sht = dss.insertSheet("Sheet0");
sht.getRange(1,1,vs.length,vs[0].length).setValues(vs);
PropertiesService.getScriptProperties().setProperty("start",sr + 50);
}