Can I add a script to a Google Sheet using another script?

Question

I have a script populate() to run an automation script on Google Sheets. I want this script to be run on over a hundred Google Sheets files. Is there a way to write a script to add to (or at least run) my populate() script on all those files? I prefer to be able to add the script to each file because we may need to run the scripts multiple times for each file. Otherwise, I will have to manually copy/paste the script to each sheet, which takes time.

Update: Removed the part about converting Excel files to Google Sheets because I found the answer for that on another thread here.

Write a script compatible with google to open each file in the folder, apply your script and dave. Run it, drink coffee and wait to finish. — Solar Mike
I think that the question title doesn't match the question body. What does populate()? Also please add a brief description of your search/research efforts as is suggested in How to Ask. — Rubén

Mateo Randwolf Mateo Randwolf · Accepted Answer · 2020-06-03T09:28:54

Solution

From what I have understood from your post, your question is about how to convert a series of Excel files inside a Drive folder into Google Sheets to then execute a function in them.

For that, you could iterate over all the files of your folder and convert them with the use of Drive.insert from the Drive API. For that, you will have to enable in your script the Advanced Google Services. If you do not know how to do so, please follow these indications.

The follow script performs what I think you are aiming for, it has self explanatory comments:

function convertExcelToGoogleSheets() {
  
  // Id of the folder where you have all your excel files
  var folderId = 'FOLDERID';
  var folder = DriveApp.getFolderById(folderId);
  
  // Get all the files in your folder and iterate over them
  var files = folder.getFiles();
  // For each file:
  while (files.hasNext()){
    var file = files.next();
    
    // Get its blob (content)
    var blob = file.getBlob();
    
    // Create the resource to insert the Google Sheet file. Put the same name
    // as the original and set the type of the file to Google sheets and its 
    // parent folder to where the excel files are located
    var resource = {
      title: file.getName(),
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{id: folderId}],
    };
    // User Drive API to insert the actual file and get its ID
    var id = Drive.Files.insert(resource, blob).getId();
    // Whatever function you want to perform in the newly created Google Sheet
    // pasing the parameter of the file id
    myScript(id);  
    }
}

// Set the first cell of all the converted sheets to "IT WORKED"
function myScript(id){
  SpreadsheetApp.openById(id).getActiveSheet().getRange('A1').setValue('IT WORKED!');
}

EDIT: If you want to run a script for each of this converted sheets you can simply call it in the while loop and pass the new file id as a parameter to then be able to use the script (remember to encapsulate this script in a function).

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

Can I add a script to a Google Sheet using another script?

1 Answers

Solution