Google Sheets as Database

Question

I'm trying to enter some data on a sheet in google sheets create a button that submits data onto another sheet. The other sheet will be like my database.

I have little to no JS experience. I'm able to create the button and link it my script but after that, I'm lost. This code worked well to get data to my database sheet. The problem is that the data stays on the same row and when I run the script the old data is erased.

function transfer() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];

  var range1 = source.getRange("B2");
   range2.copyValuesToRange(destination,3,3,2,2);
    range2.clearContent();

   var range2 = source.getRange("C2");
    range2.copyValuesToRange(destination,4,4,2,2); 
     range2.clearContent();

   var range3 = source.getRange("D2");
    range3.copyValuesToRange(destination,5,5,2,2);
     range3.clearContent();

   var range4 = source.getRange("C2");
    range4.copyValuesToRange(destination,4,4,2,2); 
     range4.clearContent();


  }

The other issue is I don't want the cells to be empty so I tried to set an alert.

var range1 = source.getRange("A2");
 range1.copyValuesToRange(destination,2,2,2,2);
  if (range1 ==!"");
   Browser.msgBox("Please Enter A Date");

It prompted the msg box but still copied the data over.

Last I would like range1 to be like a unique ID. So if I put a value in A2 on my source sheet then it will auto-fill the other cells.

Here's the link if that helps.

https://docs.google.com/spreadsheets/d/1Zi7Oc0f5AlxcRRoFMM1Q1VkkM1Co6Yo8yYBY1TvkQMc/edit?usp=sharing

Cooper Cooper · Accepted Answer · 2018-12-06T23:51:52

I recommend that you spend more time with the documentation. Personally, I've never considered putting buttons directly on the spreadsheet. I'd rather use the menu or a dialog or a sidebar. Also you generally pick up a lot of performance to replace the use of getValue() with getValues() where appropriate.

But here's another way to approach the problem your working on. Perhaps it will give something to think about. Have fun. Your buttons do look nice though. Very nice work.

function onOpen(){//This will put a post button on a menu
  SpreadsheetApp.getUi().createMenu('My Menu')
  .addItem('Post Data', 'postData')
  .addToUi();
}

function postData() {//this will append data fromm 'DataEntry' to 'DB' with a timestamp spliced into it.
  var ss=SpreadsheetApp.getActive();
  var sh1=ss.getSheetByName('DataEntry');
  var sh2=ss.getSheetByName('DB');
  var rg1=sh1.getRange(sh1.getLastRow(),1,1,sh1.getLastColumn());
  var vA=rg1.getValues();
  vA[0].splice(0,0,Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"E MMM dd, yyyy HH:mm:ss"));
  sh2.appendRow(vA[0]);
}

This is what my DateEntry tab looks like:

This is what my DB tab looks like:

The post just adds the last row in DataEntry to the row after the last row in DB.

Google Sheets as Database

1 Answers