CopyTo: How to push data/copy a cell from one google spreadsheet to another google spreadsheet using google apps script?

Question

I look for a solution to copy a specific cell value in the Source spreadsheet from tab "Sum all" to another Spreadsheet to the Target Spreadsheet Tab "Copy all". It should work every time i change the Value of Cell G10. Access to the Target sheet is granted before i enter any Value to G10.

(Source Tab Name is "Sum all:G10" - Sheet has 10 different Tabs)

(Target Tab Name is "CopyData:T12" - Sheet has 10 different Tabs)

I usually get the value, then set the value in separate steps. There is a getValue() and setValue() method. But in order to use those methods they must be chained to a range. And in order to get a range, you need to get the sheet. And in order to get the sheet, you need to get the spreadsheet. Google documentation - getValues() — Alan Wells

Eric Dauenhauer Eric Dauenhauer · Accepted Answer · 2016-02-04T01:54:00

Easy way

Use the built-in IMPORTRANGE() function in Google Apps:

In your Target cell, type the following formula

=IMPORTRANGE("FILE_ID_HERE","Sum all!G10:G10")

The syntax for this function is

=IMPORTRANGE("FILE_ID","SHEET_NAME!RANGE_START:RANGE_END")

When you first type in this function, you'll get an error in the cell. Simply click on it and select "Allow" to link the two sheets together. This error will occur even if it is the same spreadsheet. This function can link two separate spreadsheets, too, as long as you have edit access to both.

Hard Way

I'm assuming from your question that you want to copy values to and from the same spreadsheet document, but to different cells that are located on different sheets of the spreadsheet. The Google Apps Script API calls tabs "sheets" and the overall document "spreadsheet".

First, open the script editor

Open your spreadsheet that you'd like to make this script for.
Select "Tools" in the toolbar, then "Script Editor"

Second, make a function for onEdit.

Making a function named onEdit will create a function that runs every time the edit trigger is fired, using a no-authorization "simple trigger". Google Sheets automatically sends this event every time a cell is edited by a user. The argument e for the function is the event passed by the trigger.

function onEdit(e) {
    // Get the sheet named "Sum all" from the active spreadsheet (i.e. the one you are editing)
    var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sum all");
    // Returns the active cell
    var cell = source .getActiveCell();

    // Compare to see if its the right cell you're looking for
    // getRow and getColumn methods return integers for the row and column of the cell
    // A = 1, B = 2, ... G = 7
    if (cell.getRow() == 10 && cell.getColumn() == 7) {
        // If its the right cell, copy to the other cell
        var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopyData");

        // set the value of the desired cell in the target sheet
        target.getRange("T12").setValue(cell.getValue());
    }
}

Third, save the script

Save the script, reload the file, and test it out.

If your tabs are on different spreadsheets

Change this line:

var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopyData");

to this:

var target = SpreadsheetApp.openById("FILE_ID").getSheetByName("CopyData");

and insert the file ID for the target spreadsheet where I've written FILE_ID.
You will also need to use an "installed trigger", since a simple trigger cannot open a remote spreadsheet. To do this, change the name (so it is no longer a simple trigger function), and follow the steps here

CopyTo: How to push data/copy a cell from one google spreadsheet to another google spreadsheet using google apps script?

1 Answers

Easy way

Hard Way