How to get values from all unprotected cells from a protected google sheet to another sheet?

Question

I created a "form" in google sheets and I protected the sheet except for some editable ranges that require an answer. I need the values from those unprotected cells and copy them in a row to another sheet.

I found a solution from another forum that simply selects all the concerned fields. But I don't want to hardcode the range / cell location because I might add need to edit it in the future.

This is what I have so far:

function cpydata() {
    const ss = SpreadsheetApp.getActiveSpreadsheet(); // <<< current spreadsheet
    const s1 = ss.getSheetByName("Template");
    const s2 = ss.getSheetByName("Summary");
    const data = ss.getDataRange().getValues();
    const out = [data[1][2], data[2][2], data[3][2], data[4][2], data[5][2], data[6][2]];
    s2.getRange(s2.getLastRow()+1, 1, 1, out.length).setValues([out]);
}

function clearUnprotectedRanges() {
   var sheet = SpreadsheetApp.getActiveSheet();  
   var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
   for (var i = 0; i < protections.length; i++) {
     var unProtectedRanges = protections[i].getUnprotectedRanges();
     for (var j = 0; j < unProtectedRanges.length; j++) {
       unProtectedRanges[j].clearContent();
     }
   }
   Browser.msgBox("Form has been cleared.") 
}

Screenshot of the File

The cells in red are unprotected ranges.

Any ideas?

will this be something like a backup in Summary sheet? in that context why not just copy whole range like B2:H(last_non-empty_row) from template to summary sheet? — player0
– player0, Commented Jun 6, 2024 at 15:25
@player0 Each cell there points to a column for further data processing. There are also spaces in between and I might add more questions or columns as necessary. — Cookie
– Cookie, Commented Jun 6, 2024 at 15:49
This is what I used to clear the contents of the unprotected cells. But I can't seem to get the values from these then paste them into the other sheet. function clearUnprotectedRanges() { var sheet = SpreadsheetApp.getActiveSheet(); var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET); for (var i = 0; i < protections.length; i++) { var unProtectedRanges = protections[i].getUnprotectedRanges(); for (var j = 0; j < unProtectedRanges.length; j++) { unProtectedRanges[j].clearContent(); } } Browser.msgBox("Form has been cleared.") } — Cookie
– Cookie, Commented Jun 6, 2024 at 16:40

Tedinoz · Accepted Answer · 2024-06-12 05:20:47Z

You have a "form" on "Template".

When the form has been completed, you want to copy the results to a row on "Summary". But you are concerned about future changes, so you I don't want to hardcode the range / cell location.

This answer proposes defining column and row values on "Template" as variables; this means that new columns and/or rows can be added and a simple to the script will adjust for the changed question location.

function copyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // <<< current spreadsheet
  var s1 = ss.getSheetByName("Template"); // Form Data
  var s2 = ss.getSheetByName("Summary"); // Target sheet
  
  // get the Form data
  var data = s1.getDataRange().getValues()
  // Logger.log(data) // DEBUG

  // create a termporary array to hold the data fields
  var templateData = []
  // variables to identify content
  // Columns numbers
  var people = 2
  var process = 4
  var system = 4
  // Questions/Rows
  // QUALITY - 3,4,5
  // FREQUENCY - 7,8
  // EMPATHY - 10,11,12,13
  // RESOLUTION - 15
  var questionRows = [3,4,5,7,8,10,11,12,13,15]

  // build the responses by column
  questionRows.forEach(r => templateData.push(data[r-1][people-1]))
  questionRows.forEach(r => templateData.push(data[r-1][process-1]))
  questionRows.forEach(r => templateData.push(data[r-1][system-1]))
  // Logger.log(templateData) // DEBUG

  // get last row in Summary column B
  var bVals = s2.getRange("B1:B").getValues();
  var bLast = bVals.filter(String).length;
  // Logger.log("DEBUG: The last line of content in Column B of s2 = "+bLast)

  // save data to Summary
  // define the range
  var range = s2.getRange(+bLast+1,2,1,30)
  // Logger.log("DEBUG: the target range = "+range.getA1Notation())
  range.setValues([templateData])

}

TEMPLATE

SUMMARY

Collectives™ on Stack Overflow

How to get values from all unprotected cells from a protected google sheet to another sheet?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related