Bagaimana melindungi pemformatan sel tetapi hanya mengizinkan entri data di lembar google

Cara untuk mengatasi masalah tersebut adalah dengan menggunakan kombinasi fungsi kustom di google spreadsheet dan google script. Saya akan menggunakan contoh untuk mencoba mengatasi solusi untuk masalah Anda

Perlindungan formula

Pemahaman saya adalah Anda akan meminta pengguna memasukkan entri baru ke baris baru. Ini berarti Anda dapat menerapkan rumus secara otomatis ke baris baru ini, yang mungkin mencegah mereka mengubah rumus tersebut sejak awal

1) Untuk menerapkan rumus ke baris baru, gunakan ArrayFormula. Contoh di bawah ini akan menjumlahkan nilai dari kolom F ke Kolom I di setiap baris

=arrayFormula((F:F+G:G+H:H+I:I))
 read as = F1+G1+H1+I1 in row 1, =F2+G2+H2+I2 in row2 and so on

Anda bisa mendapatkan lebih rumit dan menghapus 0 nilai dari lembar seperti itu

=arrayFormula(if((F:F+G:G+H:H+I:I) = 0,"",(F:F+G:G+H:H+I:I)))

Rincian lebih lanjut dapat ditemukan di sini. Dengan melakukan itu, Anda meniadakan kebutuhan pengguna untuk mengetikkan rumus dan karenanya mengurangi modifikasi. ArrayFormula secara otomatis menerapkan/memperbaiki rumus bahkan ketika baris baru dimasukkan atau dihapus

2) Karena arrayFormula akan diterapkan di baris pertama (atau baris tertentu). Tetapkan Perlindungan di baris pertama (atau baris tertentu itu) sehingga hanya Anda yang dapat memodifikasinya dan yang secara tidak langsung berarti tidak ada yang dapat mengubah rumus apa pun di lembar. Ini tidak mencegah pengguna untuk memodifikasi di bawah baris 1 yang memengaruhi cara kerja arrayFormula, itulah mengapa Anda menggunakan perlindungan rumus kolom (dijelaskan di bawah)

Proteksi Formasi

1) Mirip dengan di atas, Anda dapat mengatur baris pertama menjadi baris master yang akan digunakan untuk memformat baris yang tersisa. Karena Anda juga akan menguncinya, tidak seorang pun kecuali Anda yang dapat memodifikasinya

2) Untuk menyalin pemformatan secara otomatis, Anda dapat menggunakan pemicu onEdit(e) di google sheets. Kode di bawah melakukan hal itu

function onOpen(e){
 var ui = SpreadsheetApp.getUi()
 ui.createMenu("Format sheet").addItem("Format", "setFormat").addToUi()

}
function onEdit(e){           //This is activated each time a modification happens in the sheet
  var ss = SpreadsheetApp.getActive()
  var sheet =ss.getActiveSheet()
  if(sheet.getSheetName() == "Sheet1"){
  var entryRange = e.range
  var range  = sheet.getRange(1,entryRange.getColumn(),1,entryRange.getNumColumns())              //This will be you range to get the formatting from row "1" and corresponding column based on the column being edited
  Logger.log(entryRange.getA1Notation())
  range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns()+entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows()+entryRange.getRow()-1)
  Logger.log(entryRange.getColumn())
  if(entryRange.getColumn() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
    if (entryRange.getRow() != 1){                                  //The columns in row "1" will be allowed to modified
    e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
    }
  }
  }
}

function setFormat(){
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getActiveSheet()
  var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 2, dataRange.getNumRows())

}

Salin yang di atas ke Editor skrip Anda di google sheets dan simpan. Kemudian muat ulang lembar google Anda. Pada titik ini, apa pun yang Anda ubah di baris "1" tidak akan terpengaruh. Namun, setiap perubahan pada baris di bawahnya akan memicu onEdit untuk secara otomatis menyalin format dari baris pertama dan menerapkannya ke baris tersebut di kolom terkait

Perlindungan Rumus Kolom. Kode ini di dalam fungsi onEdit akan mencegah modifikasi pada kolom tertentu, kolom 10 dalam kasus ini. Tetapi dapat dimodifikasi untuk melindungi kolom apa pun. (Untuk memahami mengapa kita membutuhkan ini, baca Formula Protection. 2) di atas)

if(entryRange.getColumn() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
        if (entryRange.getRow() != 1){                                  //The columns in row "1" will be allowed to modified
        e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
        }
      }

Ini akan menggantikan nilai lama di kolom 10 dari kolom 2 dan seterusnya. Namun, jika seseorang menyalin seluruh baris baru, yang juga mengubah baris 10, ini tidak akan mencegahnya untuk dimodifikasi.

Anda dapat memodifikasinya agar Anda dapat mengakses untuk memodifikasi baris 10 seperti itu

if(entryRange.getColumn() == 10 && Session.getEffectiveUser().getEmail() != "Your Email Here")

Catatan. Jika Anda melakukan modifikasi ini, pastikan untuk menjalankan fungsi onEdit dari editor skrip satu kali untuk memberinya izin untuk mengakses ID email Anda

Terakhir, Saat Anda memuat ulang lembar, Anda akan melihat item menu baru bernama Format Lembar, Anda dapat menggunakan ini untuk menerapkan format di baris "1" ke semua baris di bawahnya. Yang dilakukan oleh fungsi setFormat(). Ini adalah kode fallback, jika orang mengubah pemformatan setelah pemicu onEdit menyetel pemformatan. onEdit(e) tidak dipicu saat pengguna mengubah format sel

Bagaimana Anda mengunci atau melindungi sel setelah memasukkan atau memasukkan data ke dalam spreadsheet Google?

Apakah ada cara untuk mengunci pemformatan di Google Sheets?

Bagaimana cara melindungi pemformatan di Excel tetapi mengizinkan entri data?