0

I'm using Siri Shortcuts to input Data with google scripts to a google sheet. I've found ways to add data but it used appendRow which adds data to a new Row.

I'm running 2 scripts, one which adds data to the first three columns in a new row.

I want the next script to write Data on the next 2 columns, instead of starting on a new row but I have no idea how to do that.

I have absolutely no idea what I'm doing, I'm very new to coding so I'm sincerely sorry for all the trouble.

I searched and came across scripts to find the last row that contains data, but I have no idea how to add data to it.

I found this page, but I still have no idea: Determining the last row in a single column

This is the code I'm using to add data to the first 3 columns in a new row:

var ss = SpreadsheetApp.openById("").getSheetByName('');


function doGet(e) {

  var mood = JSON.parse(e.parameters.mood)

  var formattedTime = Utilities.formatDate(new Date(), "GMT+530", "h:mm a");
  var formattedDate = Utilities.formatDate(new Date(), "GMT+530", "MM/dd/yyyy");

 ss.appendRow([formattedDate,formattedTime,mood]);

}
Mia
  • 1
  • 1
  • Your question seems to imply that you think that appendRow() won't handle text. That's not correct. It just doesn't copy formatting. – Cooper May 25 '19 at 23:06

1 Answers1

0

There are different ways to achieve what you need, one of them is to build a customizable function that inserts data to the last row in a sheet:

/**
 * Gets Sheet by its name from a Spreadsheet (by id);
 * @param {String} id Spreadsheet's id;
 * @param {String} name Sheet name;
 * returns {Sheet} this sheet;
 */
function getSheet(id,name) {
  var ss = SpreadsheetApp.openById(id);
  var sh = ss.getSheetByName(name);
  return sh;      
}

//test function;
function testInsert() {
  var sh = getSheet('yourSpreadsheetId','yourSheetName');
  var data = [1,2,3,4,5,7]; //an array of values;
  insert(sh,data,1);
}

/**
 * Inserts values to the last row;
 * @param {Sheet} sheet to use;
 * @param {Array} array of values to set;
 * @param {Integer} startCol column to start from;
 */
function insert(sheet,data,startCol) {
  var lrow   = sheet.getLastRow();
  var lrange = sheet.getRange(lrow,startCol,1,data.length)
  lrange.setValues([data]);
}
Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
  • What should I write in `getSheet()`? I searched but couldn't find anything, What's the global variable? – Mia May 25 '19 at 19:02
  • Global variable is the one you define outside function scope - in your sample code you write your sheet to `ss`, simply reference it instead of sh ( getSheet() can be any function that fetches and returns a Spreadsheet ) – Oleg Valter is with Ukraine May 25 '19 at 19:23
  • I've updated the answer with an example of a function that fetches the Sheet you need - it is usually better to keep logic that fetches a resource in a function that you can reuse on-demand – Oleg Valter is with Ukraine May 25 '19 at 19:43
  • Thank you so much for the detailed response. I can now get the array values on the columns I like.. but how do I add the JSON response to the place where the `var data = [1,2,3,4,5,7]` is kept? I tried researching and they were talking about converting it into a 2D array?? Should I ask this as a different question, perhaps? – Mia May 26 '19 at 10:52
  • My code implicitly converts 1D array into a 2D, so do not worry about it. If I understand you correctly, simply invoke `insert()` inside your `doGet()` function with your array as a second parameter - or do you mean something else? – Oleg Valter is with Ukraine May 26 '19 at 11:48