I'm floundering again. The last question regarded a speed-up for writing values from a spreadsheet:sheet row to specific cells on a custom form (derived from a sheet). This time, I'm trying to do the reverse...Copy data from the list of cells on the form to a specific row on the datasheet. The statements to 'push' data to the array work. Then, I get an error when I run the batchUpdate code (below) that I cannot figure out. Any help would be appreciated. ...OR should I just revert to item-by-item copying from the array to the datasheet (~5seconds)?
"Error
GoogleJsonResponseException: API call to sheets.spreadsheets.values.batchUpdate failed with error: Invalid value at 'data[0]' (type.googleapis.com/google.apps.sheets.v4.ValueRange), "5693123-Q5aa"
Invalid value at 'data[1]' (type.googleapis.com/google.apps.sheets.v4.ValueRange), "Fredi"
Invalid value at 'data[2]' (type.googleapis.com/google.apps.sheets.v4.ValueRange), "Yeast"
...followed by another 53 lines of similar messages for each data cell."enter code here
//** Function to copy data from the specific cells of the User form ("User Contact Info Form") to the spreadsheet sheet ("VolunteerListTbl") into Row# (DatabaseRow2Update) */
function Ex_UpdateRecord(){
var Ss=SpreadsheetApp.getActiveSpreadsheet();
var ShUserForm=Ss.getSheetByName("User Contact Info Form");
var DataSheet = Ss.getSheetByName("VolunteerListTbl");
var DatabaseRow2Update = ShUserForm.getRange("A11").getValue();
var LastColNumber = DataSheet.getLastColumn();
var RangesToUpdate = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14", "F15", "I10", "I12", "I14", "I16", "F17", "B19", "F20", "I20", "F22", "I22", "F24", "C27", "B22", "C23", "B24", "B26", "F28", "C29", "B28", "B34", "B32", "G32", "G36", "C36", "B40", "B42", "E40", "H40", "E42", "H42", "B44", "E44", "H44", "E46", "H46", "C48", "B52", "B54", "F52", "B56", "F54", "F56", "F11", "C17", "D19", "D20"]; //List of cells containing data to be updated on the "VolunteerListTbl" sheet
//Routine to create the array of values to be used to update the sheet's row values
var FormValuesArray = [];
for (var j=0; j<RangesToUpdate.length; j ){ //this loop will add each successive value for the cells listed above...to fill the array (1x56 values)
FormValuesArray.push(ShUserForm.getRange(RangesToUpdate[j]).getValue());
Logger.log("Array value for j= " j "; is " ShUserForm.getRange(RangesToUpdate[j]).getValue() " for the range " RangesToUpdate[j])
}
DataSheet.getRange(DatabaseRow2Update,1,1,LastColNumber).activate();//Added this to specify the row where the data was to be updated
Sheets.Spreadsheets.Values.batchUpdate({data: FormValuesArray, valueInputOption: "USER_ENTERED"}, Ss.getId());
return
}CodePudding user response：
I believe your goal is as follows.
- You want to copy the values from the range of
RangesToUpdatein the sheet "User Contact Info Form" to the same range in the sheet "VolunteerListTbl". - You want to achieve this using Sheets API.
When I saw your script, FormValuesArray.push(ShUserForm.getRange(RangesToUpdate[j]).getValue()); is used in a loop. In this case, the process cost becomes high. For this, I would like to propose retrieving the values using Sheets API.
And, about your this script Sheets.Spreadsheets.Values.batchUpdate({data: FormValuesArray, valueInputOption: "USER_ENTERED"}, Ss.getId());, it seems that FormValuesArray is an array like ["value1", "value2",,,]. The request body of the method of spreadsheets.values.batchUpdate is {"data": [{"range": "", "values": []}]}. Ref I thought that this might be the reason of your issue. In your request body, it is required to set the range and values properties.
When above points are reflected to your script, it becomes as follows.
Modified script:
function Ex_UpdateRecord() {
var Ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = Ss.getId();
var srcSheetName = "User Contact Info Form";
var dstSheetName = "VolunteerListTbl";
var RangesToUpdate = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14", "F15", "I10", "I12", "I14", "I16", "F17", "B19", "F20", "I20", "F22", "I22", "F24", "C27", "B22", "C23", "B24", "B26", "F28", "C29", "B28", "B34", "B32", "G32", "G36", "C36", "B40", "B42", "E40", "H40", "E42", "H42", "B44", "E44", "H44", "E46", "H46", "C48", "B52", "B54", "F52", "B56", "F54", "F56", "F11", "C17", "D19", "D20"];
// Retrieve values from "dstSheetName".
var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: RangesToUpdate.map(e => `'${srcSheetName}'!${e}`) });
// Create a request body.
var FormValuesArray = values.valueRanges.map((e, i) => ({ range: `'${dstSheetName}'!${RangesToUpdate[i]}`, values: e.values }));
// Put the values to "VolunteerListTbl".
Ss.getSheetByName(dstSheetName).getRange(DatabaseRow2Update, 1, 1, LastColNumber).activate();
Sheets.Spreadsheets.Values.batchUpdate({ data: FormValuesArray, valueInputOption: "USER_ENTERED" }, ssId);
}
- In this modified script, the range of
RangesToUpdatein "User Contact Info Form" sheet is copied to the same range ofRangesToUpdatein "VolunteerListTbl" sheet.
References:
Added 1:
From your following replying,
The number of data fields from the source (the "User Contact Info Form") and the destination are the same. It is the locations on the sheets that differ. For example, cell C11 on the form copies to column 1 on the destination sheet ("VolunteerListTbl") and cell F10 on the form copies to column 2 on "VolunteerListTbl"... and so on. The arrangement on the form was due to groupings that allowed a cohesive display. The RangesToUpdate (source) are in the same order as the cells on the destination table. Thanks for making the effort to clarify.
I thought that you wanted to put the values to a row in the sheet "VolunteerListTbl". In this case, how about the following modified script?
Modified script:
function Ex_UpdateRecord() {
var Ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = Ss.getId();
var srcSheetName = "User Contact Info Form";
var dstSheetName = "VolunteerListTbl";
var RangesToUpdate = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14", "F15", "I10", "I12", "I14", "I16", "F17", "B19", "F20", "I20", "F22", "I22", "F24", "C27", "B22", "C23", "B24", "B26", "F28", "C29", "B28", "B34", "B32", "G32", "G36", "C36", "B40", "B42", "E40", "H40", "E42", "H42", "B44", "E44", "H44", "E46", "H46", "C48", "B52", "B54", "F52", "B56", "F54", "F56", "F11", "C17", "D19", "D20"];
// Retrieve values from "dstSheetName".
var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: RangesToUpdate.map(e => `'${srcSheetName}'!${e}`) });
// Create an array for putting to spreadsheet.
var FormValuesArray = values.valueRanges.map(e => e.values[0][0]);
// Put the values to "VolunteerListTbl".
var dstSheet = Ss.getSheetByName(dstSheetName);
dstSheet.getRange(1, 1, 1, FormValuesArray.length).setValues([FormValuesArray]);
// or dstSheet.appendRow(FormValuesArray);
}
- In this script, the retrieved values from
User Contact Info Formis put to a row of the sheetVolunteerListTbl. - In this case, the values are put using
setValuesandappendRow. - When you want to append the row to the destination sheet, please use
dstSheet.appendRow(FormValuesArray).
Added 2:
When I saw your sample Spreadsheet, I noticed that your destination sheet name is VolSearchResult Tbl. But your script uses VolunteerListTbl. I thought that this might be the reason of your issue. And, I tested my sample script reflecting the modification suggested in my comment, I confiemed no error occurs. For this, please test the following script.
Sample script:
function Ex_UpdateRecord() {
var Ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = Ss.getId();
var srcSheetName = "User Contact Info Form";
var dstSheetName = "VolSearchResult Tbl";
var RangesToUpdate = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14", "F15", "I10", "I12", "I14", "I16", "F17", "B19", "F20", "I20", "F22", "I22", "F24", "C27", "B22", "C23", "B24", "B26", "F28", "C29", "B28", "B34", "B32", "G32", "G36", "C36", "B40", "B42", "E40", "H40", "E42", "H42", "B44", "E44", "H44", "E46", "H46", "C48", "B52", "B54", "F52", "B56", "F54", "F56", "F11", "C17", "D19", "D20"];
// Retrieve values from "dstSheetName".
var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: RangesToUpdate.map(e => `'${srcSheetName}'!${e}`) });
// Create an array for putting to spreadsheet.
var FormValuesArray = values.valueRanges.map(e => e.values && e.values[0] ? e.values[0][0] : ""); // This modification is from my comment.
// Put the values to "VolSearchResult Tbl".
var dstSheet = Ss.getSheetByName(dstSheetName);
dstSheet.appendRow(FormValuesArray);
}
CodePudding user response：
Based on the suggestions from Tanaike, a solution was developed that reduced the execution time from ~1300ms down to ~400-600ms. The code that worked (adapted to my application for copying revised data to 2 different tables in Google Apps Script) are shown in the lines of code. The last two lines can be interchanged, with the first being used for updating an existing record and the second (using the 'append' method) is for adding a new record.
var srcSheetName = "User Contact Info Form";// 'Source' sheet name
var dstSheetName = "Volunteer List Tbl"; // 'Destination' sheet name was "VolunteerListTbl";
var dstSheet2Name = "VolSearchResult Tbl"; // 'Destination' sheet name was "VolunteerListTbl";
// Retrieve values from "srcSheetName" (source)
var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: FormRangesToCopy.map(e => `'${srcSheetName}'!${e}`) });
// Create an array for putting to spreadsheet. /// Method 2
var FormValuesArray = values.valueRanges.map(e => e.values && e.values[0] ? e.values[0][0] : ""); // This modification is from my comment.
// Copy the values to "Volunteer List Tbl".
var dstSheet = Ss.getSheetByName(dstSheetName);
var dstSheet2 = Ss.getSheetByName(dstSheet2Name);
dstSheet.getRange(DataSheetRowDisplayed,1,1,FormValuesArray.length).setValues([FormValuesArray]); //Worked!!!
dstSheet2.getRange(SearchRowDisplayed,1,1,FormValuesArray.length).setValues([FormValuesArray]); //Worked!!!
// OR Append a new record to table
//a dstSheet.appendRow(FormValuesArray); //Append Row adds a 'New' row.