CODEWITHSUNDEEP
google-apps-script
Dwight Cousin
Dwight Cousin

Reputation: 23

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

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)

Upvotes: 1

Views: 6262

Answers (1)

Eric Dauenhauer
Eric Dauenhauer

Reputation: 759

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

  1. Open your spreadsheet that you'd like to make this script for.
  2. 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

Upvotes: 1

Related Questions