How to change the cell value of multiple sheets in Google Sheets [Google App Script]?

0
votes

I have many sheets {(for example) 01.01, 01.02, 01.03 ..... 12.30, 12.31}.
Sheets are duplicated by one sheet, so all sheets of cell address J1 is the same date value.

I want to change J1 cell value by Google App Script.
For example:

J1 value in 01.01 sheet is 2020.1.1

J1 value in 01.02 sheet is 2020.1.2

J1 value in 01.03 sheet is 2020.1.3

J1 value in 12.30 sheet is 2020.12.30

J1 value in 12.31 sheet is 2020.12.31

Here is my Google Sheet

1
google-apps-scriptgoogle-sheets
Updated the post by removing fluff and formatting and the post – Nimantha

1 Answers

1
votes

Seeing your sheet it is in korean so I don't know exactly how you want your information to be formatted.

But what I understand is that you want to format the same cell in every sheet using the name of the sheet. I can explain how to get started in that. 

function updateCell() {
    const a1NotationRange = "J1"; // The range to be modified in every sheet

    // Retrieve the spreadsheet
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    // Get the full list of sheets and change the value of `a1NotationRange`
    // And execute a function for every single sheet
    const sheets = ss.getSheets();
    sheets.forEach((sheet) => {
        const sheetName = sheet.getName();

        // If you want to modify the format to be inserted you should expand
        // this snippet of code here.

        sheet.getRange(a1NotationRange).setValue(sheetName);

    })
}

The concept is very simple, get the all the sheets inside a variable. And then iterate through them to change the range reflected in a1NotationRange.

Reference:

If you are new to javascript programming you could use some of these links to guide you in this code:

If you need more help using the Apps Script services: