CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Taizooooon
Taizooooon

Reputation: 91

Transferring value from one spreadsheet to another

I am trying to create a spreadsheet that can allow user to enter upto 100 rows of information and when they click submit button script will enter that data into master data spreadsheet.

I have written script that works and transfers data to master data sheet and I am using openbyID to open target sheet.

The problem is that without access to master data sheet user can not open the sheet to add data. if there is a way to add data to a spreadsheet without having access to that spreadsheet would be ideal for my needs.

Please let me know if that is possible.

Once again I am currently using OpenByID to open spreadsheet and would like allow users to add data to master spreadsheet without direct access to spreadsheet.

Help will be greatly appreciated.

Please let me know if you have any questions.

Thank you Khokhar

My Code:

Sorry I am not sure how to write my script as web app.

please see below my script.. can you please help me explain how to change it to work as web app?

Thank you

function CopyDataToMaster() {
  var inf = SpreadsheetApp.getActiveSpreadsheet();
  var inf_sheet = inf.getSheetByName("Info");
  var tt_key = inf_sheet.getRange("A4").getValue();
  var SiteName = inf_sheet.getRange("A2").getValue();
  var d = new Date();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tt = SpreadsheetApp.openById(tt_key);

  var ss_sheet = ss.getSheetByName("Report");
  var tt_sheet = tt.getSheetByName("Master-Data-Sheet");
  var FilledBy = ss_sheet.getRange("C5").getValue();

  var tt_last_row = tt_sheet.getLastRow();
  var tt_work_row = tt_last_row+1;

  for (var i=1; i<=100 ; i++) {
    var dDr = ss_sheet.getRange(i+10, 2);
    var dD = dDr.getValue();
    if (dD!="") {
      var nMr = ss_sheet.getRange(i+10, 3);
      var nM = nMr.getValue();      
      var aMr = ss_sheet.getRange(i+10, 4);
      var aM = aMr.getValue();      
      var bWr = ss_sheet.getRange(i+10, 5);
      var bW = bWr.getValue();      
      var bSr = ss_sheet.getRange(i+10, 6);
      var bS = bSr.getValue();      
      var tDr = ss_sheet.getRange(i+10, 7);
      var tD = tDr.getValue();      
      var mOr = ss_sheet.getRange(i+10, 8);
      var mO = mOr.getValue();      
      var bOr = ss_sheet.getRange(i+10, 9);
      var bO = bOr.getValue();      
      var dCr = ss_sheet.getRange(i+10, 10);
      var dC = dCr.getValue();

      tt_sheet.insertRowAfter(tt_work_row-1);

      tt_sheet.getRange(tt_work_row,1).setValue(Utilities.formatDate(new Date(),+5.5, "MM/dd/yyyy HH:mm:ss"));
      tt_sheet.getRange(tt_work_row,2).setValue(SiteName);
      tt_sheet.getRange(tt_work_row,3).setValue(FilledBy);
      tt_sheet.getRange(tt_work_row,4).setValue(dD);
      tt_sheet.getRange(tt_work_row,5).setValue(nM);
      tt_sheet.getRange(tt_work_row,6).setValue(aM);
      tt_sheet.getRange(tt_work_row,7).setValue(bW);
      tt_sheet.getRange(tt_work_row,8).setValue(bS);
      tt_sheet.getRange(tt_work_row,9).setValue(tD);
      tt_sheet.getRange(tt_work_row,10).setValue(mO);
      tt_sheet.getRange(tt_work_row,11).setValue(bO);
      tt_sheet.getRange(tt_work_row,12).setValue(dC);

      dDr.clearContent();
      nMr.clearContent();
      aMr.clearContent();
      bWr.clearContent();
      bSr.clearContent();
      tDr.clearContent();
      mOr.clearContent();
      bOr.clearContent();
      dCr.clearContent();

      tt_work_row = tt_work_row+1;
    }
  }
  Browser.msgBox("Thank you", "Information has been submitted",Browser.Buttons.OK)
}

Upvotes: 0

Views: 239

Answers (2)

eddyparkinson
eddyparkinson

Reputation: 3700

Protect a sheet or two? - You can protect sheets, so some sheets can be edited and others can't. You can control who can edit which sheets. ... Also maybe use database formulas to pull the data across. Less work than writing code. Because there is a history of all edits, you can use this see all changes made to the sheets.

Upvotes: 0

user1864610
user1864610

Reputation:

You can create a script as a web app, which can access Google documents and Spreadsheets. If you do this you can set the permissions on the script so that a user running it has your permissions rather than their own. If you have access to the master spreadsheet than your script will, but your users won't.

The details are on the Google Developers site. Look down the page for Permissions and WebApps

Upvotes: 0

Related Questions