Trigger an appscript to copy new data to a set location

Johnny Quest 0
Viewed 76

I have a folder that is constantly being upload with a new google sheet on fridays. I was wondering if there is a way to trigger a script to copy the name and the data of the new sheet in this forlder and place it in another location into a master sheet.

The name of the sheet is constantly been updated with the date of when it was upload. the master sheets name and location will never change.

function runsies() {
 
  copyRange(
    "the google sheet ID", // google ID of the master sheet
    "the name of the sheet!A2:H", //name of the master sheet!, rnag to copy from mastersheet
    "getNewestFileInFolder", //Destination
    "detination!A2" // name of the Dump sheet! and the location of the dump file. 
  );
 
}

function copyRange(sourceRange, destinationID, destinationRangeStart) {

  const sourceSS = SpreadsheetApp.openById(getNewestFileInFolder());
  const sourceRng = sourceSS.getRange(sourceRange)
  const sourceVals = sourceRng.getValues();

  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destStartRange.getSheet();

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVals.length,
    sourceVals[0].length
  );

  destRange.setValues(sourceVals);
  SpreadsheetApp.flush();
};
eventtrigger triggers google-sheets google-apps-script
1 Answers

Try:

function getNewestFileInFolder() {
  var arryFileDates, file, fileDate, files, folder, folders,
    newestDate, newestFileID, objFilesByDate;

  folders = DriveApp.getFoldersByName('yourFolderName');
  arryFileDates = [];
  objFilesByDate = {};

  while (folders.hasNext()) {
    folder = folders.next();

    files = folder.getFilesByType("application/vnd.google-apps.spreadsheet");
    fileDate = "";

    while (files.hasNext()) {//If no files are found then this won't loop
      file = files.next();

      fileDate = file.getLastUpdated();
      objFilesByDate[fileDate] = file.getId(); //Create an object of file names by file ID

      arryFileDates.push(file.getLastUpdated());
    }

    if (arryFileDates.length === 0) {//The length is zero so there is nothing
      //to do
      return;
    }

    arryFileDates.sort(function (a, b) { return b - a });

    newestDate = arryFileDates[0];

    newestFileID = objFilesByDate[newestDate];

    var ss = SpreadsheetApp.openById(newestFileID);
  };
  return newestFileID;
};

How about using this function to return the ID of the latest spreadsheet file in the Drive Folder. Then use this as an input for your importRange() function:

function copyRange(sourceRange, destinationID, destinationRangeStart) {

  const sourceSS = SpreadsheetApp.openById(getNewestFileInFolder());
  const sourceRng = sourceSS.getRange(sourceRange)
  const sourceVals = sourceRng.getValues();

  const destinationSS = SpreadsheetApp.openById(destinationID);
  const destStartRange = destinationSS.getRange(destinationRangeStart);
  const destSheet = destStartRange.getSheet();

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVals.length,
    sourceVals[0].length
  );

  destRange.setValues(sourceVals);
  SpreadsheetApp.flush();
};

I advise you change the function name for your importRange() since there is a built in IMPORTRANGE function in google sheet just to avoid any possible conflicts and confusion

Update: Updated code upon discussion in comments to avoid confusion.

function copyRange() {
  const sourceSS = SpreadsheetApp.openById("SourceSpreadsheetID");
  const sourceRng = sourceSS.getRange("A1:A");
  const sourceVals = sourceRng.getValues();
  // console.log(sourceVals);

  const destinationSS = SpreadsheetApp.openById(getNewestFileInFolder());
  const destStartRange = destinationSS.getRange("A1:A");
  const destSheet = destStartRange.getSheet();

  const destRange = destSheet.getRange(
    destStartRange.getRow(),
    destStartRange.getColumn(),
    sourceVals.length,
    sourceVals[0].length
  );

  destRange.setValues(sourceVals);
  SpreadsheetApp.flush();
};

References:

Dan F0
Related