Apps Script: how do you copy values from specific columns to a different spreadsheet via a time trigger, button press or sheet update?

Question

I need to copy the values of multiple ranges of data from my main spreadsheet into another spreadsheet and then copy a different range of data back to the main spreadsheet.

Here are my example spreadsheets:

The main spreadsheet (two sheets: 1 - A sheet of pasted values alongside columns of user comments. 2 - A sheet with a button that runs the Apps Script)

The live data spreadsheet (pulls and formats the required data from other sheets).

Previously, I’ve used .copyTo() to copy the values of data and paste them elsewhere on the same spreadsheet. However, this method can’t be used to copy data to a different spreadsheet. Copying data from multiple ranges is also causing me issues. Here is my code:

function RefreshSheetData() { 

// 1a. Run the script when a button is pressed (Main Spreadsheet - ‘Update report button!A3’) 
// 1b. Run the script at a certain time (every Monday at 10 AM) 
// 1c. Run the script when data in a sheet is replaced with new data via a formula – NOT POSSIBLE. 
// 2. Copy email address and user comments (Main Spreadsheet - Editable report - J3:J & AM3:AR) 
// 3. Clear the “Updated user comments” sheet below the header row (Live data Spreadsheet - Updated user comments - A2:G) 
// 4. Paste the values (Live data Spreadsheet - Updated user comments - A2:G) 
// 5. Clear the “Editable report” below the header rows (Main Spreadsheet - Editable report – B3:AR) 
// 6. Copy the Live sheet (which should now include the most recent user comments via array vlookup) (Live data Spreadsheet - live data – A3:AQ) 
// 7. Paste the values (Main Spreadsheet - Editable report - B3:AR) 
// 8. Add the (United Kingdom) time and date (Main Spreadsheet - Update report button - A10) 
// 9. Add the time and date (Live data Spreadsheet - Updated user comments - J1) 

ScriptApp.newTrigger('RefreshSheetData') 
.timeBased() 
.onWeekDay(ScriptApp.WeekDay.MONDAY) 
.atHour(10) 
.create(); 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var startSheet = ss.getSheetByName('Editable report');
var sourceRange = startSheet.getRangeList(['J3:J', 'AM3:AR']);
var sourceValues = sourceRange.getValues();

var target = SpreadsheetApp.openById('1OHQHefYvE4vZZPr8jgziy_L3-UBf1WSoKzMWQ8LUz6w');
var targetSheet = target.getSheetByName('Updated user comments');

var clearTargetRange = targetSheet.getRange('A2:G').clearContent();

var targetRange = targetSheet.getRange('A2').setValues(sourceValues);  

var liveTargetSheet = target.getSheetByName('Live data');
var liveSourceRange = liveTargetSheet.getRange('A3:AQ').getValues();

var clearMainRange = startSheet.getRange('B3:AR').clearContent();
var startRange = startSheet.getRange('B3').setValues(liveSourceRange);

SpreadsheetApp.getActive().getRange('A10').setValue(new Date());  
targetSheet.getRange('J1').setValue(new Date()) 

} 

Any tips would be appreciated. Thank you.

Part of my question was how to trigger a script when a formula output changes. That’s not possible. Neither .onEdit or .onChange triggers work as they only respond to user actions. They won't run when the value of an IMPORTRANGE or alternative formula changes.

Answer 1

Here some suggestions referring to each of the steps mentioned in your Apps Script file

1. 1b. To run the script at a certain time: please use the installable trigger "Time driven"

   1c. To run the script when data in the sheet is updated: please use the onEdit trigger

   1d. You can combine all triggers by simply adding as many, as required.

2. To copy and paste data, you just need the methods getValues() and setValues(), which you were using already, the important thing is that you chose the "to copy" range correctly.

3. You can clear a range with clear()

4. See 3.

5. You can copy a sheet with copyTo(), however keep in mind that if using this method, your data in the copied sheet will automatically be updated if there is a change in the original sheet. If you want the values to remain static, you have to copy and paste them with copyValues() and setValues().

6. See 2. and 5.

7. See here how to get and format the date in Apps Script

8. Assign the date to a variable and use setValue()

I encourage you to try and build the script based on those steps yourself, the Apps Script documentation provides you good reference and guidance, how to do so. If you encounter specific problems during one of the steps which you cannot solve with the documentation, feel free to ask!