Create files from list of values in Google sheets

Question

Here is my situation :

I have a spreadsheet with values in the 1st column,
I would like to create a spreadsheet for every value in this file
and have these files in a specific folder.

My code seems to be working well but crashes after only creating tens of files (I have thousands) Any advice on how to improve that ? I am still pretty new to Google script

//Create files from sheet
function iterateThroughRows() {
 var data = SpreadsheetApp.getActive()
 var data = sheet.getDataRange().getValues();
 data.forEach(function (row) {
  var folderId = "MYFOLDERID"
  var resource = {
  title: row[0], // First Cell/first element of array data
  mimeType: MimeType.GOOGLE_SHEETS,
  parents: [{ id: folderId }]
}
var fileJson = Drive.Files.insert(resource)
var fileId = fileJson.id

var revisions = Drive.Revisions.list(fileId); 
var items = revisions.items; 
var revisionId =items[items.length-1].id; 
var resource = Drive.Revisions.get(fileId, revisionId); 
resource.published = true;
resource.publishAuto = true;
resource.publishedOutsideDomain = true;
Drive.Revisions.update(resource, fileId, revisionId); 
 });
}

Please add more details about how the script crash, if there is a error message add it textually. Also add abrie description of your search efforts as is suggested in [ask]. — Rubén, Aug 18 '22 at 14:50
Thank you for your answer @Rubén the script is reaching timeout limit. I have read the Google script best practices and posts here about this but I have really limited knowledge and I am a little lost on how to improve iteration. If you can point me in the right direction, I would be happy to do some more reading ! — Antoine, Aug 18 '22 at 15:01

Rubén · Answer 1 · 2022-08-18T15:29:10.590

The problem is the number of files to be created so you have to choose a method to handle how many files will be created on each execution. Since you are pretty new to Apps Script you might want to use the "simplest solution" (minimal changes to your script). How about this?

Instead of putting the data in a single sheet, split it in multiple sheets, then process one sheet at a time.

To make your script able to work with the above approach replace

 var data = SpreadsheetApp.getActive()
 var data = sheet.getDataRange().getValues();

by

 var sheet = SpreadsheetApp.getActiveSheet();
 var data = sheet.getDataRange().getValues();

Activate the sheet to be processed, then run the script.
Repeat until all the sheets were processed.

If your script crashes again, move the rows that were not processed to a new sheet.

Google app script timeout ~ 5 minutes?

If this approach is not good for you please read the related question : https://stackoverflow.com/q/14450819/1595451 — Rubén, Aug 18 '22 at 15:34

Create files from list of values in Google sheets

1 Answers1