[GAS] Operational efficiency! How to Synchronize Value Data in Two Google Spreadsheets in Two Ways [1/2]

It’s a familiar and convenient Google Spreadsheet, but this time it’s a synchronization script? I will show you how to create a state like this. For example, if you want to synchronize the state of two files (to make the data in the sheet the same state), it seems that such a function is not found in the standard, but it can be realized using GAS / GoogleAppsScript is.

Synchronize two data?

For example, when A1 of “Sheet 1” in “A file” is edited, A1 of “Sheet 1” in “B file” changes to the same cell information. Conversely, when A1 of “Sheet 1” in “B file” is edited, A1 cell of “Sheet 1” in “A file” changes to the same state as B file.

prepare two files

First, prepare two Google Sheets files. You can name them anything you like, but I’ll call them “File A” and “File B” to make it easy to understand.

Create new script

Create a new AppsScript for each file. Select Extensions > AppsScript.

Let’s change the project name to make it easier to understand.

Read sheet data

First, write a script as follows to access the data in the spreadsheet. * The ID and sheet name must be changed according to the environment of the created file.

Change the function name to make it easier to understand.

const ID1 = "XXXXXXXXXXXXX"; //ファイルAのスプレッドシートID
const ID2 = "XXXXXXXXXXXXX"; //ファイルBのスプレッドシートID
const SHEET_NAME = "シート1";

function myFileSync() {
  const spreadsheet1 = SpreadsheetApp.openById(ID1).getSheetByName(SHEET_NAME);
  const spreadsheet2 = SpreadsheetApp.openById(ID2).getSheetByName(SHEET_NAME);
}

With just this, you will be able to access the data in the container-bound Google Spreadsheet. Set this for both file A and file B.

Write data from file A to file B

I try to write data from file A to file B. Use ” getRange”, ” getValue” and ” setValue” to get the value of cell A1 and set it to cell A1 of file B. Now run the script in this state.

function myFileSync() {
  const spreadsheet1 = SpreadsheetApp.openById(ID1).getSheetByName(SHEET_NAME);
  const spreadsheet2 = SpreadsheetApp.openById(ID2).getSheetByName(SHEET_NAME);

  //ファイルA→ファイルB 転記
  const text1 = spreadsheet1.getRange(1,1).getValue();
  spreadsheet2.getRange(1,1).setValue(text1);
}

Since you will be asked “Approval is required”, we will proceed from checking the authority. *The next screen has been written many times in the past, so please refer to other articles.

The data in cell A1 will be in the same state

By executing this script, ” data was written from A1 cell of file A to A1 cell of file B “, so the contents of cell A1 will be the same.

set the trigger

As it is, it will be posted when the script is executed manually, so let’s set a trigger to automatically execute it.

Set is complete.

Editing a cell will automatically write to it

Now, when you edit the value of cell A1 in file A (strictly speaking, even in other cells…), the script will run and the value will be automatically transferred to the cell.

Same content! !

Write data to file B → A

File A → File B posting is now complete. After that, set File B → File A. It’s easy once you get there! Set the opposite state.

function myFileSync() {
  const spreadsheet1 = SpreadsheetApp.openById(ID1).getSheetByName(SHEET_NAME);
  const spreadsheet2 = SpreadsheetApp.openById(ID2).getSheetByName(SHEET_NAME);

  //ファイルB→ファイルA 転記
  const text1 = spreadsheet1.getRange(1,1).getValue();
  spreadsheet1.getRange(1,1).setValue(text1);
}

Rewrite spreadsheet1 in spreadsheet1.getRange(1,1).getValue(); to spreadsheet2.

Rewrite spreadsheet1 in spreadsheet1.getRange(1,1).setValue(text1); to spreadsheet2. Now the spreadsheet that gets the cell value and the spreadsheet that writes to the cell is reversed.

Let’s rewrite the value of A1 cell in file B.

A1 cell in file A also got the same value! !

You now have bi-directional A1 cell autoposting! In other words, it will be in a state like “synchronization”.

Click here for the completed form

File A

const ID1 = "XXXXXXXXXXXXX"; //ファイルAのスプレッドシートID
const ID2 = "XXXXXXXXXXXXX"; //ファイルBのスプレッドシートID
const SHEET_NAME = "シート1";

function myFileSync() {
  const spreadsheet1 = SpreadsheetApp.openById(ID1).getSheetByName(SHEET_NAME);
  const spreadsheet2 = SpreadsheetApp.openById(ID2).getSheetByName(SHEET_NAME);

  //ファイルA→ファイルB 転記
  const text1 = spreadsheet1.getRange(1,1).getValue();
  spreadsheet2.getRange(1,1).setValue(text1);

}

File B

const ID1 = "XXXXXXXXXXXXX"; //ファイルAのスプレッドシートID
const ID2 = "XXXXXXXXXXXXX"; //ファイルBのスプレッドシートID
const SHEET_NAME = "シート1";

function myFileSync() {
  const spreadsheet1 = SpreadsheetApp.openById(ID1).getSheetByName(SHEET_NAME);
  const spreadsheet2 = SpreadsheetApp.openById(ID2).getSheetByName(SHEET_NAME);

  //ファイルB→ファイルA 転記
  const text1 = spreadsheet2.getRange(1,1).getValue();
  spreadsheet1.getRange(1,1).setValue(text1);

}

summary

“Synchronize Cell A1” is now complete! However, if this is left as it is, only the data in the A1 cell will be in a state like synchronization, so it is necessary to set the other cells to be synchronized in the same way.

This continuation has become long, so I would like to introduce it again next time.

Please refer to it