Google-sheets – Small Edit on Import Range Script for Mass Edits on Google Spreadsheets

google sheetsgoogle-apps-script

I found a great video on YouTube about editing Google Sheets in bulk, however the script writes over formulas with cell values. I'd like to have formulas be edited from the master sheet rather than writing over with cell values. Is this possible?

Script:

function getdata() {
  var files = DriveApp.getFolderById("FOLDER ID HERE").getFiles()
    while (files.hasNext()) {
      var file = files.next();
      var shoot = SpreadsheetApp.openById(file.getId());

      var sourcesheet = SpreadsheetApp.getActive().getSheetByName("SHEET NAME HERE");
      var sourcerange = sourcesheet.getRange('RANGE HERE');
      var sourcevalues = sourcerange.getValues();

    var destsheet = shoot.getSheetByName('SHEET NAME HERE'); 
    var destrange = destsheet.getRange('RANGE HERE'); 
destrange.setValues(sourcevalues);

Best Answer

Yes, it's possible. Instead of setValues(sourcevalues) you could use setFormulas(formulas)

Related Solutions

Google Sheets – How to Create Daily Duplicate of Master Sheet

Here is one approach. The script should be attached to the master spreadsheet (Tools > Script editor) and set to run on daily basis (Edit > Current project's triggers). To run, it needs the destination folder Id (the part of folder URL after /folders/).

The new copy is named like "original file name 2017-07-25 19:11:23" which also serves the purpose of timestamping. This is actually UTC time... there are other options, such as

var timestamp = new Date().toLocaleString(); (returns July 25, 2017 3:17:45 PM EDT)

var timestamp = new Date().toDateString(); (returns Tue Jul 25 2017).

Both of the above depend on local date conventions of the owner of master sheet.

function duplicateSpreadsheet() {
  var folder = DriveApp.getFolderById("folder Id here");
  var timestamp = new Date().toISOString().replace("T", " ").split(".")[0];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var file = DriveApp.getFileById(ss.getId());
  file.makeCopy(folder).setName(file.getName() + " " + timestamp);
}

Date               Close        Date               Close        Average
 1/3/2017 16:00:00  786.14       1/3/2017 16:00:00  786.14      786.14
 1/4/2017 16:00:00   786.9       1/4/2017 16:00:00   786.9       786.9
 1/5/2017 16:00:00  794.02       1/5/2017 16:00:00  794.02      794.02
 1/6/2017 16:00:00  806.15       1/6/2017 16:00:00  806.15      806.15
 1/9/2017 16:00:00  806.65       1/9/2017 16:00:00  806.65      806.65
1/10/2017 16:00:00  804.79      1/10/2017 16:00:00  804.79      804.79

Note:
Google spreadsheet functions are only recalculated while the spreadsheet is open, so using a script to be ran while the spreadsheet is not opened by anyone will retrieve the values saved the last time the spreadsheet was online-opened/synced offline changes.

References

Best Answer

Related Solutions

Google Sheets – How to Create Daily Duplicate of Master Sheet

Further reading

Google Sheets – Automatically Store Updated GOOGLEFINANCE Values with Script

Related Topic