Google Sheet - Turn on Offline mode unpon opening

Question

My objective here is to let multiple users change the file at the same time without disrupting others. I think that the offline mode is the answer. That's why I'm searching for a way to turn on the offline mode editing when a user opens it.

Maybe offline mode is not the most efficient way, I'm open to all suggestions.

I have provided an answer below. Kindly check if that works for you. — NightEye, Feb 12 '21 at 17:24

score 0 · Answer 1 · answered Feb 12 '21 at 16:36

What you can do instead is copy the sheet and let them work on the duplicated sheet. Use the code below.

Code:

function openDuplicatedSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetName = spreadsheet.getName();

  // Duplicated file id
  var fileId = SpreadsheetApp.create("Copy of " + spreadsheetName).getId();
  var destination = SpreadsheetApp.openById(fileId);
  // Rename sheet so it won't be duplicated
  destination.renameActiveSheet("temp-sheet-to-be-removed-later)");
  var sheets = spreadsheet.getSheets();
  // Copy the sheets excluding the script so it won't duplicate infinitely
  sheets.forEach(function (sheet, index) {
    sheet.copyTo(destination);
    // Use sheet name from the original sheet
    var sheetName = sheet.getSheetName();
    destination.getSheets()[index + 1].activate();
    destination.renameActiveSheet(sheetName);
  });
  // Remove default Sheet1 which was renamed earlier to avoid duplication of sheet name
  destination.deleteSheet(destination.getSheets()[0]);

  var htmlOutput = HtmlService
    .createHtmlOutput('<a href="https://docs.google.com/spreadsheets/d/' + fileId + '/" target="_blank">' + spreadsheetName + '</a>')
    .setWidth(350) 
    .setHeight(50); 
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Click to open duplicated sheet');
}

Trigger:

Prompt upon opening:

Output:

This will create a duplicate/copy of the content of the file (excluding the script) and should be accessible to their own drives.

Note:

There is a little delay with the pop window. Just wait for it to show up.
Use the duplicated file to avoid multiple duplicates of the original file.
The duplicated file won't further duplicate as its script was not included in the copy.

Reference:

duplicate spreadsheet