CODEWITHSUNDEEP
javascriptgoogle-sheets
Samantha Garcia
Samantha Garcia

Reputation: 510

Google script to compile data from multiple different spreadsheets that are copies of the same file

I have a spreadsheet that I made multiple copies of to input information for different people. I was trying to find a way to compile the data without having to call each spreadsheet one by one since they were all copied from the same file and have a similar title. Is there a way to search for that title and compile data from any spreadsheet that contains it in google sheets?

If it is helpful, this is the spreadsheet I am trying to compile data from. I am trying to compare strengths/weaknesses across the board without having to go into each individual person's spreadsheet one by one: https://docs.google.com/spreadsheets/d/1hiw3Z_BVpJY6J0osn6-XRS9IpieotH8CRIsiacmW068/edit?usp=sharing

This is what I have so far:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
  ss.addMenu("Search Google Drive", searchMenuEntries);
}

function search() {
  // Prompt the user for a search term
  var searchTerm = Browser.inputBox("Enter the String to Search For:");

  // Get the active spreadsheet and the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // Set up the spreadsheet to display the results
  var headers = [["Unit 8 Project Data", "KU", "MI", "SE", "CO", "File Type", "URL"]];
  sheet.clear();
  sheet.getRange("A1:G1").setValues(headers);
  // Search the files in the user's Google Drive for the search term
  // See documentation for search parameters you can use 
  // https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)
  var files = DriveApp.searchFiles("title contains 
'"+searchTerm.replace("'","\'")+"'");

  // create an array to store our data to be written to the sheet 
  var output = [];
  // Loop through the results and get the file name, file type, and URL
  while (files.hasNext()) {
    var file = files.next(); 
    var name = file.getName();
    var startRow = 9;  // First row of data to process
    var numRows = 10;   // Number of rows to process
    var type = file.getMimeType();
    var url = file.getUrl();
    var id = file.getId();
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var dataRange = sheet.getRange(startRow, 6, numRows, 1)
    var data = dataRange.getValues();
    var KU = data[0][0];
    var MI = data[3][0];
    var SE = data[6][0];
    var CO = data[9][0];
    // push the file details to our output array (essentially pushing a row of data)
    output.push([name, KU, MI, SE, CO, type, url]);
  }
  // write data to the sheet
  sheet.getRange(2, 1, output.length, 7).setValues(output);
}

It is finding the spreadsheets but isn't getting the data inside the spreadsheet (which is the part I wrote myself). Those spots come out blank. I'll try to dig more when I get off, but this is what I have so far.

Upvotes: 0

Views: 209

Answers (1)

Samantha Garcia
Samantha Garcia

Reputation: 510

Figured it out! Thanks for all your help!

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}];
  ss.addMenu("Search Google Drive", searchMenuEntries);
}

function search() {
  // Prompt the user for a search term
  var searchTerm = Browser.inputBox("Enter the String to Search For:");

  // Get the active spreadsheet and the active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheeta = ss.getActiveSheet();

  // Set up the spreadsheet to display the results
  var headers = [["Unit 8 Project Data", "KU", "MI", "SE", "CO", "File Type", "URL"]];
  sheeta.clear();
  sheeta.getRange("A1:G1").setValues(headers);
  // Search the files in the user's Google Drive for the search term
  // See documentation for search parameters you can use 
  // https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String)
  var files = DriveApp.searchFiles("title contains 
'"+searchTerm.replace("'","\'")+"'");

  // create an array to store our data to be written to the sheet 
  var output = [];
  // Loop through the results and get the file name, file type, and URL
  while (files.hasNext()) {
    var file = files.next(); 
    var name = file.getName();
    var startRow = 9;  // First row of data to process
    var numRows = 10;   // Number of rows to process
    var type = file.getMimeType();
    var url = file.getUrl();
    var id = file.getId();
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var dataRange = sheet.getRange(startRow, 6, numRows, 1)
    var data = dataRange.getValues();
    var KU = data[0][0];
    var MI = data[3][0];
    var SE = data[6][0];
    var CO = data[9][0];
    // push the file details to our output array (essentially pushing a row of data)
    output.push([name, KU, MI, SE, CO, type, url]);
  }
  // write data to the sheet
  sheeta.getRange(2, 1, output.length, 7).setValues(output);
}

Upvotes: 1

Related Questions